Merge into语句的使用

Merge into 用来从一个表中选择一些数据更新或者插入到另一个表中。而最终是用更新还是用插入的方式取决于该语句中的条件。
如果N条记录条件符合，就向目标表merge_test1 更新N条记录。如果有M条记录不符合要求，就向目标表merge_test1 插入M条记录。
通过这个merge你能够在一个SQL语句中对一个表同时执行inserts和updates操作. 当然是update还是insert是依据于你的指定的条
件判断的，Merge into可以实现用B表来更新A表数据，如果A表中没有，则把B表的数据插入A表. MERGE命令从一个或多个数据源中
选择行来updating或inserting到一个或多个表 


语法如下 
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 or delete sql] 
WHEN NOT MATHED THEN [execute something else here ! ] 


我们先看看一个简单的例子，来介绍一个merge into的用法 
merge into products p 
using newproducts np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name 
when not matched then 
insert values(np.product_id, np.product_name, np.category) 






下面我们简单的举一个例子：
SQL> create table merge_test1(a number,b varchar2(20)) 
表已创建。
SQL> create table merge_test2(a number,b varchar2(20)) 
表已创建。
SQL> insert into merge_test1 values(1,'A'); 
已创建 1 行。
SQL> insert into merge_test1 values(2,'B'); 
已创建 1 行。
SQL> insert into merge_test1 values(3,'C'); 
已创建 1 行。
SQL> insert into merge_test2 values(3,'C'); 
已创建 1 行。
SQL> insert into merge_test2 values(2,'C'); 
已创建 1 行。
SQL> commit; 
提交完成。
SQL> select * from merge_test1; 
         A B
---------- --------------------
         1 A
         2 B
         3 C 
SQL> select * from merge_test2; 
         A B
---------- --------------------
         3 C
         2 C
前面的准备工作之后，用Merge对 Merge_test2进行更新
MERGE INTO MERGE_TEST2 M
USING MERGE_TEST1 N
ON (M.A = N.A)
WHEN MATCHED THEN
 UPDATE SET M.B = N.B
WHEN NOT MATCHED THEN
 INSERT (M.A,M.B) 
 VALUES (N.A,N.B); 
更新后结果：
SQL> select * from merge_test2; 
         A B
---------- --------------------
         1 A
         3 C 
         2 B 
总结：
一、Merge 的用法大致上有三种: 
1、只更新不插入
Merge into Merge_test2 M 
Using Merge_test1 N 
ON (M.A = N.A) 
WHEN MATCHED THEN 
UPDATE SET M.B = N.B; 
2、只插入不更新
MERGE INTO merge_test2 M 
USING merge_test1 N 
ON (M.A =N.A)  
WHEN NOT MATCHED THEN 
INSERT (M.A,M.B) VALUES(N.A,N.B); 
3、既插入也更新
MERGE INTO merge_test2 M 
USING merge_test1 N 
ON(M.A = N.A) 
WHEN  MATCHED THEN 
UPDATE SET M.B=N.B 
WHEN NOT MATCHED THEN 
INSERT (M.A,M.B)
 VALUES(N.A,N.B) 
二、注意事项
1、merge语句中，update不能用于更新连接的列(即ON(M.A = N.A)) 
例如：
MERGE INTO merge_test2 M 
USING merge_test1 N 
ON(M.A = N.A) 
WHEN  MATCHED THEN 
UPDATE SET M.A=N.B ,M.B=N.B 
WHEN NOT MATCHED THEN 
INSERT (M.A,M.B) VALUES(N.A,N.B) 
我们执行上面的语句(注意红色部分)就会有如下的提示：
第 3 行出现错误:
ORA-38104: 无法更新 ON 子句中引用的列: "M"."A" ，不能更新on条件里面的字段M.A。因为M.A是2个表的连接条件。
2、Using 后面不一定是表：
语法文档上写着：table,view,subquery 
也就是说也可以是一个查询语句的结果集。
3、还有一种错误就是由于关联的值不唯一引起的
比如：
在merge_test1中再插入一条数据。与之前的重复了！
SQL> insert into merge_test1 values(3,'d'); 
此时我们执行
MERGE INTO merge_test2 M 
USING merge_test1 N 
ON(M.A = N.A) 
WHEN  MATCHED THEN 
UPDATE SET M.B=N.B 
WHEN NOT MATCHED THEN 
INSERT (M.A,M.B) VALUES(N.A,N.B) 
第 1 行出现错误:
ORA-30926: 无法在源表中获得一组稳定的行。原表不能有重复的值，否则报错。




在Oracle 10g中MERGE有如下一些改进： 
1、UPDATE或INSERT子句是可选的 
2、UPDATE和INSERT子句可以加WHERE子句 
3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表 
4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行 


我们通过实例来一一看看如上的新特性 


1. UPDATE或INSERT子句是可选的 
在9i里由于必须insert into和update都要存在，也就是不是update就是insert，不支持单一的操作，
虽然还是可以曲线救国，呵呵 但是有些过于强势了。而10g里就是可选了，能符合我们更多的需求了 
比如上面的句子 
我们可以只存在update或者insert 
merge into products p 
using newproducts np 
on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name 
这里，如果匹配就更新，不存在就不管了。 


2. UPDATE和INSERT子句可以加WHERE子句 
这也是一个功能性的改进，能够符合我们更多的需求，这个where的作用很明显是一个过滤的条件，
是我们加入一些额外的条件，对只对满足where条件的进行更新和insert 
merge into products p 
using (select * from newproducts) np 
on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name
 where np.product_name like 'OL%' 
这里表示只是对product_name开头是'OL'的匹配上的进行update，如果开头不是'OL'的就是匹
配了也不做什么事情，insert里也可以加入where 


比如 
merge into products p 
using (select * from newproducts) np
on (p.product_id = np.product_id) 
when matched then 
  update set p.product_name = np.product_name 
  where np.product_name like 'OL%' 
when not matched then 
  insert values(np.product_id, np.product_name, np.category) 
  where np.product_name like 'OL%' 


这里注意比较一下，他们返回的结果行数，是有着差异的。 


3. 在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表 


merge into products p 
using (select * from newproducts) np on (1=0) 
when matched then 
update set p.product_name = np.product_name 
when not matched then 
insert values(np.product_id, np.product_name, np.category) 
个人觉得这个功能没有太大的意义，我们的insert into本身就支持这样的功能，没有必要使用merge 


4. UPDATE子句后面可以跟DELETE子句来去除一些不需要的行 
delete只能和update配合，从而达到删除满足where条件的子句的纪录 
merge into products p 
using (select * from newproducts) np 
on (p.product_id = np.product_id) 
when matched then 
  update set p.product_name = np.product_name 
  delete where p.product_id = np.product_id where np.product_name like 'OL%' 
when not matched then 
  insert values(np.product_id, np.product_name, np.category) 
这里我们达到的目的就是 会把匹配的记录的prodcut_name更新到product里，并且把product_name开头为OL的删除掉。


merge into也是一个dml语句，和其他的dml语句一样需要通过rollback和commit 结束事务。 


Merge是一个非常强大的功能，而且是我们需求里经常会用到的一个有用的功能，所以我们一定要好好的学习到。