使用merge into 来更新目标表的个别字段

--更新职业 5s
merge into cr_gr x using
(
select b.custid,c.new_grbh,b.occupation /*职业*/,nvl(d.new_bm,‘90‘) new_occupation /*新职业*/ 
from cm002@to_db2 b
left join scdy_grzhbh_dzb c on b.custid=c.old_grbh 
left join scdy_tmp_zgzy_dzb d on trim(b.occupation)=d.old_bm
where occupation<>‘ ‘
) new_tab
on (x.grbh=new_tab.new_grbh )
when matched then
update set x.zhiye=new_tab.new_occupation;

--通过构造临时表test_source，把前后的对照关系找清楚，并且可观测到
merge into test_target
using test_source
on (test_source.id = test_target.id)--注意，如果是多条件，则 on后边一定要有个括号，把各条件都括进来
when matched then update set test_target.name = test_source.name
when not matched then insert values(test_source.name,test_source.id);--最后一行可以省略