Merge into用法总结

一.Merge into用法简介

merge into 语句是insert 与update语句的结合，可以同时实现update和insert的功能。

MERGE INTO T2                 --目标表
USING Tl                      --源表
ON (Tl.NAME=T2.NAME)       --表中有没有符合on()条件中的数据，有了就更新数据，没有就插入数据
WHEN MATCHED THEN             --符合条件就更新数据
UPDATE 
SET T2.MONEY=Tl.MONEY + T2.MONEY 
WHEN NOT MATCHED THEN         --不符合条件就插入数据
INSERT 
VALUES (Tl.NAME,Tl. MONEY) ; 

二. MERGE 灵活之处

(1)UPDATE INSERT 动作可只出现其一(Oracl 9i 要求必须同时出现)

可选择仅 UPDATE 目标表：

MERGE INTO T2 
USING Tl 
ON (Tl.NAME=T2.NAME) 
WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY=Tl.MONEY+T2.MONEY; 

也可选择仅仅 INSERT 目标表而不做任何 UPDATE 动作

MERGE INTO T2 
USING Tl 
ON (Tl.NAME=T2.NAME) 
WHEN NOT MATCHED THEN 
INSERT 
VALUES (Tl.NAME,Tl.MONEY) ; 

(2)可对 MERGE 语句加条件

MERGE INTO T2 
USING Tl 
ON (Tl.NAME=T2.NAME) 
WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY=Tl.MONEY+T2.MONEY 
WHERE Tl.NAME;      --此处表示对 MERGE 的条件进行过滤

(3)可用DELETE 子句清除行

在这种情况下，首先是要获取满足 Tl.NAME=T2.NAME 的记录，如果 T2.NAME='A' 并不满足Tl.NAME＝T2.NAME 过滤出的记录集，那这个DELETE 是不会生效的。在满足的条件下,可以删除目标表的记录。

MERGE INTO T2 
USING Tl 
ON (Tl.NAME=T2.NAME) 
WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY=Tl.MONEY+T2.MONEY 
DELETE WHERE (T2.NAME='A'）;

(4)可采用无条件方式插入

方法很简单，在 ON 关键字处写上恒不等条件（如 1=2 ）后， MATCHED 语旬的 INSERT 就变为无条件 INSERT了，同于INSERT ... SELECT 的写法，具体如下：

MERGE INTO T2 
USING Tl 
ON (1=2) 
WHEN NOT MATCHED THEN 
INSERT 
VALUES (Tl.NAME,Tl.MONEY); 

三.MERGE 误区探讨

(1)无法在源表中获得一组稳定的行

在MERGE INTO T2 USING Tl ON MERGE的表达式中，如果一条 T2 记录被连接到多条 Tl 记录，就产生了 ORA 30926 错误。构造 Tl，T2 表进行如下试验，此次 Tl 表中增加了（'A'， 30) 的记录，如下：

DROP TABLE Tl; 
CREATE TABLE Tl (NAME VARCHAR2(20),MONEY NUMBER); 
INSERT INTO Tl VALUES (’ A ’, 10); 
INSERT INTO Tl VALUES (’ A ’, 30); 
INSERT INTO Tl VALUES (’ B ’, 20); 
DROP TABLE T2; 
CREATE TABLE T2 (NAME VARCHAR2(20),MONEY NUMBER); 
INSERT INTO T2 VALUES (’ A ’, 30); 
INSERT INTO T2 VALUES (’ C ’, 20); 
COMMIT 

继续执行如下：

--脚本 14-15 MERGE 无法在源表中获得一组稳定的行
MERGE INTO T2 
USING Tl 
ON (Tl.NAME=T2.NAME) 
WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY=Tl.MONEY+T2.MONEY;
ORA-30926 ：无法在源表中获得一组稳定的行

       对于Oracle 中的 MERGE 语旬，应该保证 ON 中的条件的唯一性，当 Tl.NAME='A＇时， T2表记录对应到了 Tl 表的两条记录，所以就出错了。解决方法很简单，可对 Tl 表和 T2 表的关联字段建主键，这样基本上就不可能出现上面的问题，而且一般而言， MERGE 语旬的关联字段互相有主键， MERGE 的效率将比较高！或者是将 Tl 表的 ID 列做一个聚合，这样归并成单条，也能避免此类错误。如：

MERGE INTO T2 
USING (SELECT NAME,SUM(MONEY) AS MONEY FROM Tl GROUP BY NAME) Tl       --加了Group by分组函数,避免出现单条错误
ON (Tl.NAME=T2.NAME) 
WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY=Tl.MONEY+T2.MONEY;

       但是这样的改造需要注意，因为有可能改变了最终需求。此外需要引起注意的是，在MERGE INTO T2 USING Tl ON的MERGE 表达式中 如果反过来一条 Tl 记录被连接到多条 T2记录，是可以导致多条 T2 记录都被更新而不会出错的！继续构造 Tl,T2 表进行试验，此次是在T2 表中增加了（'A'，40）的记录如下:

DROP TABLE Tl ; 
CREATE TABLE Tl (NAME VARCHAR2(20), MONEY NUMBER); 
INSERT INTO Tl VALUES ('A',10)
INSERT INTO Tl VALUES ('B' ,20) ; 
DROP TABLE T2 ; 
CREATE TABLE T2 (NAME VARCHAR2(20) , MONEY NUMBER) ; 
INSERT INTO T2 VALUES ( 'A', 30); 
INSERT INTO T2 VALUES ( 'A', 40) ; 
INSERT INTO T2 VALUES ('C', 20 ); 
COMMIT ;

继续执行,发现执行可以成功并没有报无法在源表中获得一组稳定的行的 OR-30926 错误

MERGE INTO T2 
USING Tl 
ON (Tl.NAME=T2 . NAME) 
WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY=Tl.MONEY + T2.MONEY;
COMMIT; 

(2)DELETE子句的WHERE顺序必须在最后

MERGE INTO T2 
USING Tl 
ON (Tl.NAME=T2.NAME) 
WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY=Tl.MONEY + T2.MONEY 
DELETE WHERE (T2.NAME='A')
WHERE Tl.NAME='A';
 
ORA-00933 : SQL 命令未正确结束

改为如下即可:

--脚本14-18MERGE 中 DELETE 子句的WHERE顺序
MERGE INTO T2 
USING Tl 
ON (Tl.NAME = T2.NAME) 
WHEN MATCHED THEN 
UPDATE 
SET T2 . MONEY=Tl.MONEY + T2.MONEY 
WHERE Tl.NAME = 'A'
DELETE WHERE (T2.NAME = 'A');

注：只要是MERGE 语句， UPDATE和DELETE 两者必须要出现其一，所以上面的脚本是不能省略 UPDATE 而只做 DELETE 的。另外WHERE (T2.NAME = 'A')的的括号可以省略。

(3)DELETE子句只可以删除目标表，而无法删除源表
(4)更新同一张表的数据，需担心USING 空值

SQL> SELECT * FROM T2; 
NAME  MONEY 
A       C
----------------
30     20 

       需求为对 T2 表进行自我更新,如果在 T2 表中发现 NAME = 'D'的记录，就将该记录的 MONEY字段更新为 100 ，如果 NAME = 'D' 的记录不存在,则自动增加 NAME = ‘D’ 的记录

根据语法完成如下代码：

MERGE INTO T2 
USING (SELECT * FROM T2 WHERE NAME = 'D' ) T 
ON (T.NAME=T2.NAME) 
 WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY=l00 
WHEN NOT MATCHED THEN 
INSERT 
VALUES ('D',200);

但是查询发现，本来表应该因为NAME='D'不存在而要增加记录，但是实际却根本无变化

SQL> SELECT * FROM T2; 
NAME    MONEY 
-------------
A        C
30       20 

原因是 USING 后面必须包含要更新或插入的行。而第一个 USING (SELECT * FROM t2 WHERE NAME ＝ 'D') T 中根本没有这一行，可进行如下改造巧妙实现需求：

--脚本14-20 MERGE USlNG 无记录的应对
MERGE 'INTO T2 
USING (SELECT COUNT(*) CNT FROM t2 WHERE NAME = 'D') T 
ON (T.CNT<>O) 
WHEN MATCHED THEN 
UPDATE 
SET T2.MONEY = l00 
WHEN NOT MATCHED THEN 
INSERT 
VALUES ('D', 100) ;

SQL> SELECT * FROM T2; 
NAME      MONEY 
---------------
A          30
C          20
D          100