oracle中的merge into用法解析

1.如果存在更新，不存在插入

MERGE INTO merge_target target 
USING (SELECT B.name,B.age,B.target_id FROM merge_source B) source 
ON (target.id=source.target_id) 
WHEN MATCHED THEN  
UPDATE  SET target.name = source.name,  target.age = source.age 
WHEN NOT MATCHED THEN  
INSERT(target.name,target.age) VALUES (source.name,source.age);

2.只 insert

MERGE INTO merge_target target 
USING (SELECT B.name,B.age,B.target_id FROM merge_source B) source 
ON (target.id=source.target_id) 
WHEN NOT MATCHED THEN  
INSERT(target.name,target.age) VALUES (source.name,source.age);

3.只 update

MERGE INTO merge_target target 
USING (SELECT B.name,B.age,B.target_id FROM merge_source B) source
ON (target.id=source.target_id) 
WHEN MATCHED THEN  
UPDATE  SET target.name = source.name,  target.age = source.age 

4.带where条件的更新和插入

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='江西';

5.delete 和 update

merge into target t using source s on(t.id = s.aid)
when matched then update set t.year = s.year
delete where(t.id = 2); 

 6. 无条件 insert　　

merge into target t using source s on(1 = 0) -- 设置永假匹配条件
when not matched then
insert(t.id, t.name, t.year) values(s.aid, s.name, s.year);