マルチテーブル・バッチ更新レコード

1538 ワード


 
-- 
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 -- 
        )