当前位置:Gxlcms > mysql > oraclemerge误区

oraclemerge误区

时间:2021-07-01 10:21:17 帮助过:58人阅读

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     BOSTON
2、执行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     BOSTON
4、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

人气教程排行