在进行SQL语句编写时,我们经常会遇到大量的同时进行Insert/Update的语句 ,
也就是说当存在记录时,就更新(Update),不存在数据时,就插入(Insert)。
语法:
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]
merge into bonuses d
using (select employee_id, salary, department_id from employees where department_id = 80) s
on (d.employee_id = s.employee_id)
when matched then
update set d.bonus = d.bonus + s.salary*.01
when not matched then
insert (d.employee_id, d.bonus) values (s.employee_id, s.salary*0.01);
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);
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition) WHEN MATCHED THEN
UPDATE table_name SET col1 = col_val1, col2 = col_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
用中文来解释Merge语法,就是:
在alias2中Select出来的数据,每一条都跟alias1进行 ON (join condition)的比较,如果匹配,就进行更新的操作(Update),如果不匹配,就进行插入操作(Insert)。
因此,严格意义上讲,”在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数。”
参考:
http://www.cnblogs.com/highriver/archive/2011/08/02/2125043.html