merge用法（读书笔记）

《剑破冰山》--merge用法

CREATE TABLE test092801(NAME VARCHAR2(20),money NUMBER);INSERT INTO test092801 VALUES ('A',10);INSERT INTO test092801 VALUES ('B',20);CREATE TABLE test092802(NAME VARCHAR2(20),money NUMBER);INSERT INTO test092802 VALUES ('A',30);INSERT INTO test092802 VALUES ('C',20);COMMIT;

SQL> SELECT * FROM test092801; NAME                      MONEY-------------------- ----------A                            10B                            20 SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------A                            30C                            20

需求：从test092801表更新数据到test092802，如果test092802表name字段的记录在test092801表中存在，就将money值相加，
如果不存在，则将test092801的记录插到test092802表中
用merge可以用时合并update和Insert动作，如下

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4  UPDATE  5  SET test092802.money = test092801.money+test092802.money  6  WHEN NOT MATCHED THEN  7    INSERT  8    VALUES (test092801.name,test092801.money); 2 rows merged SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------A                            40C                            20B                            20

oracle10g中merge得到改善，可以Update和Insert选其一
可选择仅更新目标表

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money;

可选择仅插入目标表

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN NOT MATCHED THEN  4    INSERT  5    VALUES (test092801.name,test092801.money);

可对merge语句加条件

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money  6    WHERE test092801.name = 'A'; 1 row merged SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------A                            10C                            20B                            20

可用delete子句清除行
前提条件，要找到满足test092801.name=test092802.name的记录，如果test092802.name = 'A'并不满足test092801.name = test092802.name
过滤出的记录集，那么delete是不会生效的。在满足前提的条件下，可以删除目标表的记录

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money+test092802.money  6    DELETE WHERE (test092802.name = 'A'); 2 rows merged SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------C                            20B                            40

可采用无条件方式Insert
在语法on 后面写上恒不等式1=2，那么insert就变成无条件Insert,同于Insert.....select,如下：

MERGE INTO test092802USING test092801 ON (1=2)WHEN MATCHED THEN  UPDATE   INSERT   VALUES (test092801.name,test092801.money);

merge语法注意点
在merge INTO test092802 ON ...的merge表达式中，如果一条test092802记录被连接到多条test092801记录，就产生ora-30926错误

SQL> DROP TABLE test092801; Table droppedSQL> DROP TABLE test092802; Table droppedSQL> CREATE TABLE test092801(NAME VARCHAR2(20),money NUMBER); Table createdSQL> INSERT INTO test092801 VALUES ('A',10); 1 row insertedSQL> INSERT INTO test092801 VALUES ('A',30); 1 row insertedSQL> INSERT INTO test092801 VALUES ('B',20); 1 row insertedSQL> CREATE TABLE test092802(NAME VARCHAR2(20),money NUMBER); Table createdSQL> INSERT INTO test092802 VALUES ('A',30); 1 row insertedSQL> INSERT INTO test092802 VALUES ('C',20); 1 row insertedSQL> COMMIT; Commit complete

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money+test092802.money; MERGE INTO test092802USING test092801 ON (test092801.name = test092802.name)WHEN MATCHED THEN  UPDATE  SET test092802.money = test092801.money+test092802.money ORA-30926: 无法在源表中获得一组稳定的行

oracle中的merge语句应该保证on中条件的唯一性，test092801.name = 'A'时，test092802表记录对应到test092801表的两条记录，所以就出错

需要注意的是，在merge INTO test092802 ON ...的merge表达式中，如果反过来，一条test092801记录被连接到多条test092802记录，是可以使
多条test092802记录都被更新而不会出错。

SQL> DROP TABLE test092801; Table droppedSQL> DROP TABLE test092802; Table droppedSQL> CREATE TABLE test092801(NAME VARCHAR2(20),money NUMBER); Table createdSQL> INSERT INTO test092801 VALUES ('A',10); 1 row insertedSQL> INSERT INTO test092801 VALUES ('B',20); 1 row insertedSQL> CREATE TABLE test092802(NAME VARCHAR2(20),money NUMBER); Table createdSQL> INSERT INTO test092802 VALUES ('A',30); 1 row insertedSQL> INSERT INTO test092802 VALUES ('A',40); 1 row insertedSQL> INSERT INTO test092802 VALUES ('C',20); 1 row insertedSQL> COMMIT; Commit complete

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money+test092802.money; 2 rows mergedSQL> COMMIT; Commit complete SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------A                            40A                            50C                            20

可以看到，为A的两条记录都被更新了。

即目标表可以有多条记录，但是源表只可以有一条记录与之对应。

delete子句的where顺序必须在最后

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money+test092802.money  6    DELETE WHERE (test092802.name = 'A')  7    WHERE test092801.name = 'A'; MERGE INTO test092802USING test092801 ON (test092801.name = test092802.name)WHEN MATCHED THEN  UPDATE  SET test092802.money = test092801.money+test092802.money  DELETE WHERE (test092802.name = 'A')  WHERE test092801.name = 'A' ORA-00933: SQL 命令未正确结束

修改如下

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money+test092802.money  6    WHERE test092801.name = 'A'  7    DELETE WHERE (test092802.name = 'A')  8    ; 2 rows merged SQL> commit; Commit complete SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------C                            20

DELETE 子句只可以删除目标表，而不可以删除源表
注意的是，无论DELETE WHERE (test092802.name = 'A')这个语句将test092802改成test092801，效果都是一样的，都是对目标表进行删除

SQL> DROP TABLE test092801; Table droppedSQL> DROP TABLE test092802; Table droppedSQL> CREATE TABLE test092801(NAME VARCHAR2(20),money NUMBER); Table createdSQL> INSERT INTO test092801 VALUES ('A',10); 1 row insertedSQL> INSERT INTO test092801 VALUES ('B',20); 1 row insertedSQL> CREATE TABLE test092802(NAME VARCHAR2(20),money NUMBER); Table createdSQL> INSERT INTO test092802 VALUES ('A',30); 1 row insertedSQL> INSERT INTO test092802 VALUES ('C',20); 1 row insertedSQL> COMMIT; Commit complete

SQL> SELECT * FROM test092801; NAME                      MONEY-------------------- ----------A                            10B                            20 SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------A                            30C                            20

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money+test092802.money  6    WHERE test092801.name = 'A'  7    DELETE WHERE (test092802.name = 'A')  8    ; 1 row merged SQL> SELECT * FROM test092801; NAME                      MONEY-------------------- ----------A                            10B                            20 SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------C                            20

可以看到目标表中的A记录被删除，如果将DELETE WHERE (test092802.name = 'A')改为DELETE WHERE (test092801.name = 'A')，是否就会把源表
的记录删除呢，试验如下：

SQL> MERGE INTO test092802  2  USING test092801 ON (test092801.name = test092802.name)  3  WHEN MATCHED THEN  4    UPDATE  5    SET test092802.money = test092801.money+test092802.money  6    WHERE test092801.name = 'A'  7    DELETE WHERE (test092801.name = 'A')  8    ; 0 rows merged SQL> SELECT * FROM test092801; NAME                      MONEY-------------------- ----------A                            10B                            20 SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------C                            20

发现其实test092801源表的记录还是保留着，仍然只是目标表（MERGE后面的就是目标表）被删除

更新同一张表的数据，需担心using的空值

SQL> DROP TABLE test092802; Table dropped SQL> SQL> CREATE TABLE test092802(NAME VARCHAR2(20),money NUMBER); Table createdSQL> INSERT INTO test092802 VALUES ('A',30); 1 row insertedSQL> INSERT INTO test092802 VALUES ('C',20); 1 row insertedSQL> COMMIT; Commit complete

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

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

SQL> MERGE INTO test092802  2  USING (SELECT * FROM test092802 WHERE NAME = 'D') t  3  ON (t.name = test092802.name)  4  WHEN MATCHED THEN  5    UPDATE  6    SET test092802.money = 100  7    WHEN NOT MATCHED THEN  8      INSERT  9      VALUES ('D',200) 10    ; 0 rows merged SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------A                            30C                            20

但是查询发现，test092802毫无变化
原因是using后面必须包含要更新或插入的行，而第一个USING (SELECT * FROM test092802 WHERE NAME = 'D') t根本没有这一行

改写如下：

SQL> MERGE INTO test092802  2  USING (SELECT COUNT(*) cnt FROM test092802 WHERE NAME = 'D') t  3  ON (t.cnt<>0)  4  WHEN MATCHED THEN  5    UPDATE  6    SET test092802.money = 100  7    WHEN NOT MATCHED THEN  8      INSERT  9      VALUES ('D',200) 10    ; 1 row merged SQL> SELECT * FROM test092802; NAME                      MONEY-------------------- ----------A                            30C                            20D                           200