通用SQL开发的利器——MERGE（中）Merge误区

梁敬彬梁敬弘兄弟出品

往期回顾：

SQL开发的利器——MERGE（上）Merge初探

三．MERGE误区探索

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

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

DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 VALUES ('A',30);
INSERT INTO T1 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

此时继续执行如下

SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  ;
ORA-30926: 无法在源表中获得一组稳定的行

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

SQL> MERGE INTO T2
  2  USING (SELECT NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  ;
Done

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

DROP TABLE T1;
CREATE TABLE T1 (NAME VARCHAR2(20),MONEY NUMBER);
INSERT INTO T1 VALUES ('A',10);
INSERT INTO T1 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

此时继续执行如下，发现执行可以成功并没有报无法在源表中获得一组稳定的行的ORA-30926错误

SQL>  MERGE INTO T2
  2      USING T1
  3      ON (T1.NAME=T2.NAME)
  4      WHEN MATCHED THEN
  5      UPDATE
  6      SET T2.MONEY=T1.MONEY+T2.MONEY
  7      ;
Done
SQL> COMMIT;
Commit complete

查看T2表，发现T2表中NAME=A的2条记录都被更新了

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

2. DELETE子句的WHERE顺序必须最后

SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  DELETE WHERE (T2.NAME = 'A')
  8  WHERE T1.NAME='A';
/
ORA-00933: SQL 命令未正确结束

改为如下即可

SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  WHERE T1.NAME='A'
  8  DELETE WHERE (T2.NAME = 'A')
  9  ;
Done

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

3. DELETE 子句只可以删除目标表，而无法删除源表

这里需要引起注意，无论DELETE WHERE (T2.NAME = ‘A’ )这个写法的T2是否改写为T1，效果都一样，都是对目标表进行删除！

SQL> SELECT * FROM T1;
NAME                    MONEY
-------------------- ------------------------------
A                            10
B                            20
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- --------------------------------
A                            30
C                            20
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  DELETE WHERE (T2.NAME = 'A' )
  8  ;
Done
SQL> SELECT * FROM T1;
NAME                      MONEY
-------------------------------------------------------
A                            10
B                            20
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- ---------------------------------
C                            20

可以看出目标表的A记录被删除了，但是如果把DELETE WHERE (T2.NAME = ‘A’ )修改为DELETE WHERE (T1.NAME = ‘A’ )，是否就会把源表的T1记录给删除了呢？试验如下：
R

ollback complete
SQL> MERGE INTO T2
  2  USING T1
  3  ON (T1.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=T1.MONEY+T2.MONEY
  7  DELETE WHERE (T2.NAME = 'A' )
  8  ;
Done
SQL> SELECT * FROM T1;
NAME                      MONEY
-------------------- ---------------------------------
A                            10
B                            20
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- --------------------------------
C                            20

发现其实T1源表的记录根本还是保留着，还只是目标表被删除了。

4. 更新同一张表的数据,需担心USING的空值

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

需求为对T2表进行自我更新，如果在T2表中发现NAME=’D’的记录，就将该记录的MONEY字段更新为100，如果NAME=D的记录不存在，则自动增加NAME=’D’的记录。
根据语法完成如下代码

SQL> MERGE INTO T2
  2  USING (SELECT * FROM t2 WHERE NAME='D') T
  3  ON (T.NAME=T2.NAME)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=100
  7  WHEN NOT MATCHED THEN
  8  INSERT
  9  VALUES ('D',200)
 10  ;
Done

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

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

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

SQL> MERGE INTO T2
  2  USING (SELECT COUNT(*) CNT FROM t2 WHERE NAME='D') T
  3  ON (T.CNT<>0)
  4  WHEN MATCHED THEN
  5  UPDATE
  6  SET T2.MONEY=100
  7  WHEN NOT MATCHED THEN
  8  INSERT
  9  VALUES ('D',100)
 10  ;
Done
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------- -----------------------------------
A                            30
C                            20
D                           100

未完待续…
通用SQL开发的利器——MERGE（下）Merge妙用

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列