ITPub博客

首页 > Linux操作系统 > Linux操作系统 > UPDATE子查询的ORA-1779错误

UPDATE子查询的ORA-1779错误

Linux操作系统 作者:lhrbest 时间:2015-07-30 09:52:56 0 删除 编辑

在对子查询执行UPDATE时,可能会遇到ORA-1779错误,这里简单讨论一下。

 

 

首先构造两个表:

SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30), DOC VARCHAR2(30));

表已创建。

SQL> CREATE TABLE T2 (ID NUMBER PRIMARY KEY, FID NUMBER, NAME VARCHAR2(30), DOC VARCHAR2(30));

表已创建。

SQL> INSERT INTO T1 VALUES (1, 'A', 'TEST1');

已创建 1 行。

SQL> INSERT INTO T1 VALUES (2, 'B', 'TEST2');

已创建 1 行。

SQL> INSERT INTO T1 VALUES (3, 'C', 'TEST3');

已创建 1 行。

SQL> INSERT INTO T2 VALUES (1, 1, 'A', 'TESTA');

已创建 1 行。

SQL> INSERT INTO T2 VALUES (2, 2, 'B', 'TESTB');

已创建 1 行。

SQL> INSERT INTO T2 VALUES (3, 3, 'C', 'TESTC');

已创建 1 行。

SQL> INSERT INTO T2 VALUES (4, NULL, 'A', 'TESTD');

已创建 1 行。

SQL> COMMIT;

提交完成。

经常有需求会根据一张表的记录更新另一张表上对于记录的字段,一般这个SQL如下所示:

SQL> UPDATE T2 SET DOC = (SELECT DOC FROM T1 WHERE ID = T2.FID)
  2  WHERE EXISTS (SELECT 1 FROM T1 WHERE ID = T2.FID);

已更新3行。

SQL> SELECT * FROM T2;

        ID        FID NAME                           DOC
---------- ---------- ------------------------------ ---------------------
         1          1 A                              TEST1
         2          2 B                              TEST2
         3          3 C                              TEST3
         4            A                              TESTD

SQL> ROLLBACK;

回退已完成。

其中WHERE条件是为了避免将连接中匹配不到的记录更新为NULL

与此等价的有另外一种写法,直接对子查询进行更新:

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.FID)
  2  SET DOC_2 = DOC_1;

已更新3行。

SQL> SELECT * FROM T2;

        ID        FID NAME                           DOC
---------- ---------- ------------------------------ ---------------------------
         1          1 A                              TEST1
         2          2 B                              TEST2
         3          3 C                              TEST3
         4            A                              TESTD

SQL> ROLLBACK;

回退已完成。

采用这种方式,SQL显得更加简洁。

不过这种方式使用不当就会造成ORA-1779错误:

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.FID)
  2  SET DOC_1 = DOC_2;
SET DOC_1 = DOC_2
    *
2 行出现错误:
ORA-01779:
无法修改与非键值保存表对应的列

造成这个错误的原因是更新的列不是事实表的列,而是维度表的列。换句话说,如果两张表关联,其中一张表的关联列是主键,那么另一张表就是事实表,也就是说另一张表中的列就是可更新的;除非另一张表的关联列也是主键,否则这张表就是不可更新的,如果更新语句涉及到了这张表,就会出现ORA-1799错误。

如果是两张表主键关联,那么无论更新那个表的字段都可以:

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.ID)
  2  SET DOC_1 = DOC_2;

已更新3行。

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.ID)
  2  SET DOC_2 = DOC_1;

已更新3行。

SQL> ROLLBACK;

回退已完成。

如果两张表都不包含主键,那么无论更新那张表的字段都会报错:

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.NAME = T2.NAME)
  2  SET DOC_1 = DOC_2;
SET DOC_1 = DOC_2
    *
2 行出现错误:
ORA-01779:
无法修改与非键值保存表对应的列


SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.NAME = T2.NAME)
  2  SET DOC_2 = DOC_1;
SET DOC_2 = DOC_1
    *
2 行出现错误:
ORA-01779:
无法修改与非键值保存表对应的列

其实这个限制的真正原因是Oracle要确保连接后更新的内容可以写到一张表中,而这就要求连接方式必须是1N或者11的连接。这样才能确保连接后的结果集数量和事实表一致。从而使得Oracle对连接后子查询的更新可以顺利的更新到事实表中。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1756209/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1350
  • 访问量
    8089142