1、 如果在d1表中有一个字段有多个，并且在使用merge是使用该字段关联会产生ORA-30926的错误，如在上一节面我们使用相关联的字段是deptno，如果在d1中出现多行deptno的一样就会报错。 --构造实验环境（延续上一节的d1、d2表）SQL update d1 set deptno = 10
1、如果在d1表中有一个字段有多个，并且在使用merge是使用该字段关联会产生ORA-30926的错误，如在上一节面我们使用相关联的字段是deptno，如果在d1中出现多行deptno的值一样就会报错。
--构造实验环境（延续上一节的d1、d2表） SQL> update d1 set deptno = 10 where deptno = 30; 1 row updated. SQL> select * from d1; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 10 SALES CHICAGO 40 OPERATIONS BOSTON2、执行merge操作，使用d1表的记录去更新d2表的记录，查看结果
MERGE INTO d2 USING d1 ON (d1.deptno = d2.deptno) WHEN MATCHED THEN UPDATE SET d2.loc = d1.loc || '...'; USING d1 * ERROR at line 2: ORA-30926: unable to get a stable set of rows in the source tables从上面得到的错误：没有稳定值。可以得出在 ON() 子句中的条件是d1.deptno = 10不具备有唯一性，所以建议在关联的列上创建主键或者创建unique index。还有一个解决办法就是将值相等的行合并成一行来处理（请注意：这样做可能会改变需求）。
3、如果将d1和d2倒过来（使用d2的记录去更新d2的记录）就不会发生ORA-30926，原因是在d2中的deptno的值是唯一。
MERGE INTO d1 USING d2 ON (d1.deptno = d2.deptno) WHEN MATCHED THEN UPDATE set d1.loc = d2.loc || '...' SQL> select * from d1; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING FU JIAN... 20 RESEARCH DALLAS 10 SALES FU JIAN... 40 OPERATIONS BOSTON4、delete子句的where必须在最后
--环境 SQL> select * from d1; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from d2; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING FU JIAN 30 SALES CHICAGO --错误示例 MERGE INTO d2 USING d1 ON (d1.deptno = d2.deptno) WHEN MATCHED THEN UPDATE SET d2.loc = d1.loc || '...' DELETE WHERE (d2.deptno = 10) WHERE d1.deptno = 10; WHERE d1.deptno = 10 * ERROR at line 7: ORA-00933: SQL command not properly ended --正确示例 MERGE INTO d2 USING d1 ON (d1.deptno = d2.deptno) WHEN MATCHED THEN UPDATE SET d2.loc = d1.loc || '...' WHERE d1.deptno = 10 DELETE WHERE (d2.deptno = 10);5、在delete 子句只可以伤处目标表，而不能删除源表，以上实验中我们用的都是delete where (d1.deptno)。
SQL> select * from d1; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from d2; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING FU JIAN 30 SALES CHICAGO --DELETE WHERE (d1.deptno = 10) MERGE INTO d2 USING d1 ON (d1.deptno = d2.deptno) WHEN MATCHED THEN UPDATE SET d2.loc = d1.loc || '...' DELETE WHERE (d1.deptno = 10) SQL> select * from d2; DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO... SQL> rollback; Rollback complete. --使用DELETE WHERE (d2.deptno = 10) MERGE INTO d2 USING d1 ON (d1.deptno = d2.deptno) WHEN MATCHED THEN UPDATE SET d2.loc = d1.loc || '...' DELETE WHERE (d2.deptno = 10) SQL> select * from d2; DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO...6、merge当引用表中没数据的解决办法
SQL> select * from d1; no rows selected SQL> select * from d2; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING FU JIAN 30 SALES CHICAGO MERGE INTO d2 USING d1 ON (d1.deptno = d2.deptno) WHEN MATCHED THEN UPDATE SET d2.loc = 'AAAAA' WHEN NOT MATCHED THEN INSERT VALUES(20, 'aaaaa', 'AAAAA') 0 rows merged. SQL> select * from d2; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING FU JIAN 30 SALES CHICAGO按道理说：当d1中没有找到数据时应该会执行INSERT语句的。但是，从上可以看到并没有执行INSERT语句。我们可以这样取巧的使用让他实行INSERT语句：
MERGE INTO d2 USING (SELECT COUNT(*) CNT FROM d1) d ON (d.cnt <> 0) WHEN MATCHED THEN UPDATE SET d2.loc = 'AAAAA' WHEN NOT MATCHED THEN INSERT VALUES(20, 'aaaaa', 'AAAAA') SQL> select * from d2; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING FU JIAN 30 SALES CHICAGO 20 aaaaa AAAAA下载本文
