INSERT INTO party_branch
SELECT UUID(),m.name,m.secreta_name,m.contacts_name,m.contact_phon,m.category_name,m.type,'admin','admin', NOW(),NOW() FROM mypary_branch m

 

SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。

UPDATE party_branch SET id =REPLACE(id,'-','')

 

必须分开执行 如果

 

INSERT INTO party_branch
SELECT  REPLACE(UUID(),'-',''),m.name,m.secreta_name,m.contacts_name,m.contact_phon,m.category_name,m.type,'admin','admin', NOW(),NOW() FROM mypary_branch m

这样生成的uuid所有数据均为相同

 

orcal截取字符串  如  bmfw.snb.qz  这个字符串  截取得到未bmfw  

select substr(loginname,0,INSTR(loginname,'.', 1, 1)-1) from GOVERNMENTSYNCUSERRECORD;   loginname为GOVERNMENTSYNCUSERRECORD表的一个字段

INSTR(loginname,'.', 1, 1)表示从第一开始查找第一个出现.的位置

orcal批量更新

merge into GOVERNMENTSYNCUSERRECORDzj711 t1
using (select b.id,b.orgcoding,b.loginname from governmentusermappingzj711 b) t
on (t1.orgcoding=t.orgcoding and t1.loginname=t.loginname)
when matched then
update set t1.mappingid = t.id;

 

delete from GOVERNMENTORGMAPPINGzj711 where id in(
select id from GOVERNMENTORGMAPPINGzj711 where ( syncorgname,orgcoding) in(
select syncorgname,orgcoding from GOVERNMENTORGMAPPINGzj711
group by syncorgname,orgcoding having count(1)>1)
and
id not in (select max(id) from GOVERNMENTORGMAPPINGzj711
group by syncorgname,orgcoding having count(*)>1)
)

 

 

 

mysql的批量更新   

 UPDATE party_member_info_0925 p,attachment a SET  p.avatar = a.id  WHERE  a.target_module = 'PartyMemberPhoto' AND p.id=a.target_id

扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄