merge into 用法小结

1.merge语法

merge [hint] into [schema.]table [t_alias] using [schema.]{table|view|subquery}[t_alias] on (condition)when matched then merge_update_clausewhen not matched then merge_insert_clause;

2.在10g开始，
(1)update,insert动作可只出现其一(在9i必须同时出现)

merge into t2using t1on (t1.name = t2.name)when matched then  update set t2.money = t1.money + t2.money;;

(2)可对merge语句加条件

merge into t2using t1on (t1.name = t2.name)when matched then  update set t2.money = t1.money + t2.money where t1.name = 'A'--满足where条件时才更新, 条件可以是t1表，也可以是t2表

(3)可用delete子句清除目标行

merge into t2using t1on (t2.name = t1.name)when matched then  update     set t2.money = t1.money + t2.money   where t1.name = 'A' delete   where t2.name = 'A'--t2.name=t1.name后满足两个where条件的t2表记录才会被删。

(4)可采用无条件方式insert

merge into t2using t1on (1 = 2)when not matched then  insert values (t1.name, t1.money);

3.merger注意的地方

(1).如果源表中有根据条件有多条记录对应目标表，会报ORA-30926:无法在源表中获得一组稳定的行错误反过来目标表有多条记录对应源表，不会报错，多条记录会更新。

(2).delete子句的where顺序必须在最后

merge into t2using t1on (t2.name = t1.name)when matched then  update     set t2.money = t2.money + t1.money delete   where (t2.name = 'A')   where t1.name = 'A' --是错误的

(3).delete子句只可以删除目标表，不可以源表

(4)更新同一张表的数据时，注意using的空值

merge into t2using (select * from t2 where name = 'D') ton (t.name = t2.name) where matched then  update set t2.money = 100when not matched then insert values('D', 200); --无法插入('D',200)记录,因为t中无此记录

可以改成下面

merge into t2using (select count(1) cnt from t2 where name = 'd') ton (t.cnt <> 0) where matched then  update set t2.money = 100when not matched then insert values('D', 200);

4.merge巧妙运用只要能查出更新后的结果集，就可以利用结果集来更新原表记录，merge+rowid方式

merge into t2using (select rowid r, num * num n from t2) ton(t2.rowid = t.r)when matched thenupdate set t2.num = t.n;