Oracle merge语句用法

MERGE语句用法：

merge <hint>
into <table_name>
using <table_view_or_query>
on (<condition>)
when matched then <update_clause>
delete <where_clause>
  when not matched then <insert_clause>
    [log errors <log_errors_clause> <reject limit <integer | unlimited>];

（1）、正常模式

MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID) 
WHEN MATCHED THEN
 UPDATE SET A.YEAR=C.YEAR 
WHEN NOT MATCHED THEN
 INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);

（2）、只update或者只insert
（2.1）、只update

merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID) 
WHEN MATCHED THEN
 UPDATE SET A.YEAR=C.YEAR;

（2.2）、只insert

merge into A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON(A.ID=C.AID) 
WHEN NOT MATCHED THEN
  insert(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR);

（3）、带条件的update或带条件的insert

merge into A_MERGE A USING (select B.AID,B.name,B.year,B.city from B_MERGE B) C  
ON(A.id=C.AID)  
when matched then
 update SET A.name=C.name where C.city != '江西'
when not matched then
 insert(A.ID,A.name,A.year) values(c.AID,C.name,C.year) where C.city='江西';

（4）、全插入insert实现

merge into C_MERGE C USING (select B.AID,B.NAME,B.City from B_MERGE B) C ON (1=0) 
when not matched then
 insert(C.ID,C.NAME,C.City) values(B.AID,B.NAME,B.City);

可以使用直接路径插入

merge /*+ append */ into C_MERGE C USING (select B.AID,B.NAME,B.City from B_MERGE B) C ON (1=0) 
when not matched then
 insert(C.ID,C.NAME,C.City) values(B.AID,B.NAME,B.City);

（5）、带delete的update
要使delete生效，匹配的记录必须通过update子句中的where条件的筛选并且还要通过delete子句中where条件的筛选。

merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
delete where (b.ms_type!=0);