Oracle merge into 的用法详解【整理】

在进行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 T1USING (SELECT '1001' AS a,2 AS b FROM dual) T2ON ( T1.a=T2.a)WHEN MATCHED THEN   UPDATE SET T1.b = T2.bWHEN 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_nameSET col1 = col_val1, col2 = col_val2

WHENNOT 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