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

本文主要是介绍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 aset 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 bwhere 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

这篇关于sql 语句阶段性总结【多表联合更新】的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---