sql 语句阶段性总结【多表联合更新】

(1)根据支行代码更新支行名称，新建表temp_sub_branch 将需要更新的数据导入，执行sql

update ONLINE_BRA_OFF_REC_SUB_BRA a
set a.SUB_BRANCH_NAME=(

 select b.SUB_BRANCH_NAME 

 from temp_sub_branch b 

 where b.SUB_BRANCH_ORG_CODE=a.SUB_BRANCH_ORG_CODE)

where exists (

 select 1 

 from temp_sub_branch b 

 where b.SUB_BRANCH_ORG_CODE=a.SUB_BRANCH_ORG_CODE)

(2)根据支行名称更新支行代码，新建表temp_sub_branch_2 将需要更新的数据导入，执行sql

update ONLINE_BRA_OFF_REC_SUB_BRA a

set a.SUB_BRANCH_ORG_CODE=(

 select b.SUB_BRANCH_ORG_CODE 

 from temp_sub_branch_2 b 

 where b.SUB_BRANCH_NAME=a.SUB_BRANCH_NAME)

where exists (

 select 1 

 from temp_sub_branch_2 b 

 where b.SUB_BRANCH_NAME=a.SUB_BRANCH_NAME)

(3)新建表temp_all，将全量数据导入，执行sql，将支行表中的线上推广码、线下推广码填充上

update online_bra_off_rec_sub_bra a
set a.online_promotion_code=(

 select  b.online_promotion_code from temp_all b

where b.sub_brach_org_code=a.sub_branch_org_code)
where (a.online_promotion_code is null) and exists (select 1 from temp_all b
where b.sub_brach_org_code=a.sub_branch_org_code) 

同理更新线下推广码

update online_bra_off_rec_sub_bra a
set a.offline_promotion_code=(select  b.offline_promotion_code from temp_all b
where b.sub_brach_org_code=a.sub_branch_org_code)
where exists (select 1 from temp_all b
where b.sub_brach_org_code=a.sub_branch_org_code)

(4)更新temp_all表，填充branch_id 和parent_branch_id的值，执行sql

 update temp_all a
set a.branch_id=(select b.id from online_bra_off_rec_bra b where b.branch_name=a.branch_name)
where exists (select 1 from online_bra_off_rec_bra b where b.branch_name=a.branch_name)

同理更新和parent_branch_id的值，执行sql

update temp_all a
set a.parent_branch_id=(

 select 

 b.id 

 from ONLINE_BRA_OFF_REC_PARENT_BRA b 

 where b.PARENT_BRANCH_NAME=a.PARENT_BRANCH_NAME)

where exists (

 select 1 

 from ONLINE_BRA_OFF_REC_PARENT_BRA b 

 where b.PARENT_BRANCH_NAME=a.PARENT_BRANCH_NAME)

(5)根据temp_all表更新二级分行表online_bra_off_rec_bra的parent_branch_id，执行sql

update online_bra_off_rec_bra a
set a.parent_branch_id=(select distinct b.parent_branch_id from temp_all b
where b.branch_name=a.branch_name)
where exists (select  1 from temp_all b where b.branch_name=a.branch_name)

(6)根据temp_all表更新支行表ONLINE_BRA_OFF_REC_SUB_BRA的BRANCH_ID，执行sql

update ONLINE_BRA_OFF_REC_SUB_BRA aset a.branch_id=(select distinct b.branch_id from temp_all bwhere b.SUB_BRANCH_ORG_CODE=a.SUB_BRANCH_ORG_CODE)where exists (select  1 from temp_all b where b.SUB_BRANCH_ORG_CODE=a.SUB_BRANCH_ORG_CODE)

(8)验证数据

select * from online_bra_off_rec_sub_bra t
where ONLINE_PROMOTION_CODE is null or offLINE_PROMOTION_CODE is not null