多表批量更新记录

 

 

--创建备份表
create table NATION_SCOREINFO_TEST as select * from NATION_SCOREINFO 
select * from nation_scoreinfo_test
--查询报名和成绩姓名不一样
select ne.uuid as neuuid ,ne.name,ns.name,ns.uuid as nsuuid from nation_examineedetail ne , nation_scoreinfo_test ns 
where
ns.isdel<>1
and ne.plan_uuid = ns.plan_uuid
and ne.uuid = ns.examinee_uuid
and ne.card_number = ns.idcard_number  --身份证
and ne.exam_card_num = ns.exam_card_num --准考证
and ne.name != ns.name  --姓名
and ne.status >=220 --生成准考证状态
and ns.plan_uuid = 'cbaa6d10-68a6-4ac2-8932-d7c00f16fd' 



--把成绩的姓名改成和报名一样姓名
update nation_scoreinfo_test ns set ns.name=(select ne.name from nation_examineedetail ne where 
        ne.plan_uuid = ns.plan_uuid
        and ne.uuid = ns.examinee_uuid
        and ne.card_number = ns.idcard_number  --身份证
        and ne.exam_card_num = ns.exam_card_num --准考证
        and ne.name <> ns.name  --姓名
        and ne.status >=220 --生成准考证状态
        )
where 
ns.isdel<>1 
and ns.plan_uuid = 'cbaa6d10-68a6-4ac2-8932-dc00f16fd' 
and exists (select 1 from nation_examineedetail ne where 
        ne.plan_uuid = ns.plan_uuid
        and ne.uuid = ns.examinee_uuid
        and ne.card_number = ns.idcard_number  --身份证
        and ne.exam_card_num = ns.exam_card_num --准考证
        and ne.name <> ns.name  --姓名
        and ne.status >=220 --生成准考证状态
        )