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