oracle中Merge into 用法（存在就跟新，不存在就删除）

merge into CT_WL_PostExpress s1
using (
select 'WT2018110602759272448' as fnumber,'2-209002590048' as crmnum from dual union all 
select 'WX2018110549581672448' as fnumber,'2-208979258110' as crmnum from dual union all 
select 'WX2018110587529977856' as fnumber,'2-208979302223' as crmnum from dual union all 
select 'WT2018110558731403264' as fnumber,'2-208896751998' as crmnum from dual union all 
select 'WT2018110554201296896' as fnumber,'2-208891899604' as crmnum from dual union all 
select 'WX2018110557824727040' as fnumber,'2-208894440859' as crmnum from dual union all 
select 'WT2018110568172222464' as fnumber,'2-208883718864' as crmnum from dual union all 
select 'WT2018110529687171072' as fnumber,'2-208884644125' as crmnum from dual union all 
select 'WX2018110536026955776' as fnumber,'2-208884701023' as crmnum from dual union all 
select 'WT2018110581237602304' as fnumber,'2-208854396285' as crmnum from dual union all 
select 'WX2018110429946036224' as fnumber,'2-208881874771' as crmnum from dual union all 
select 'WX2018110456027856896' as fnumber,'2-208851412636' as crmnum from dual union all 
select 'WX2018110477468149760' as fnumber,'2-208851588619' as crmnum from dual union all 
select 'WT2018110450589442048' as fnumber,'2-208852117143' as crmnum from dual union all 
select 'WX2018110417925066752' as fnumber,'2-208975726852' as crmnum from dual union all 
select 'WX2018110473499658240' as fnumber,'2-208850006355' as crmnum from dual union all 
select 'WT2018110431233728512' as fnumber,'2-208852008886' as crmnum from dual union all 
select 'WX2018110451024463872' as fnumber,'2-208852591483' as crmnum from dual union all 
select 'WT2018110455055994880' as fnumber,'2-208852523573' as crmnum from dual union all 
select 'WX2018110433819086848' as fnumber,'2-208853015956' as crmnum from dual union all 
select 'WX2018110437300629504' as fnumber,'2-208853211784' as crmnum from dual union all 
select 'WT2018110404961185792' as fnumber,'2-208855379636' as crmnum from dual union all 
select 'WT2018110440964130816' as fnumber,'2-208854843137' as crmnum from dual union all 
select 'WT2018110324033966080' as fnumber,'2-208764202545' as crmnum from dual union all 
select 'WT2018110393100773376' as fnumber,'2-208786914002' as crmnum from dual union all 
select 'WT2018110379131893760' as fnumber,'2-208765436019' as crmnum from dual union all 
select 'WX2018110355057756160' as fnumber,'2-208766635999' as crmnum from dual union all 
select 'WT2018110397075212288' as fnumber,'2-208766685538' as crmnum from dual union all 
select 'WT2018110314685392896' as fnumber,'2-208766600966' as crmnum from dual union all 
select 'WT2018110313100339200' as fnumber,'2-208856803128' as crmnum from dual union all 
select 'WX2018110366354851840' as fnumber,'2-208785056329' as crmnum from dual union all 
select 'WX2018110351722954752' as fnumber,'2-208785847136' as crmnum from dual union all 
select 'WX2018110394669547520' as fnumber,'2-208785848662' as crmnum from dual union all 
select 'WT2018110390959542272' as fnumber,'2-208788597865' as crmnum from dual union all 
select 'WX2018110386605111296' as fnumber,'2-208788641276' as crmnum from dual union all 
select 'WX2018110304873138176' as fnumber,'2-208788390017' as crmnum from dual union all 
select 'WX2018110300740069376' as fnumber,'2-208666258855' as crmnum from dual union all 
select 'WX2018110327879661568' as fnumber,'2-208668435264' as crmnum from dual union all 
select 'WX2018110293836650496' as fnumber,'2-208659637649' as crmnum from dual ) s2
on (s1.CFOtherNum = s2.fnumber)
WHEN MATCHED THEN 
  update set s1.cfcrmwlorder=s2.crmnum
  
  
 

上面是自己写的。

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
    INSERT (a,b) VALUES(T2.a,T2.b);

 

这是用法