Oracle - 更新连接 - 非关键字保留表

Question

我试图复制 Ingres 的 "update tbl1 from tbl2" 命令，但是 Oracle 中并不存在这个命令。

所以我使用了 "update (select tbl1 join tbl2...)" 命令。两个表都定义了主键，而且我认为我的连接已经唯一地标识了行，但是我仍然得到了 "ORA-01779：无法修改映射到非保留键表的列" 错误。

以下是经过适当匿名化的表定义和我要执行的更新：

CREATE TABLE tbl1
(
   ID decimal(11) NOT NULL,
   A varchar2(3) NOT NULL,
   B float(7),
   CONSTRAINT tbl1_pk PRIMARY KEY (ID,A)
)
;

CREATE TABLE tbl2
(
   ID decimal(11) NOT NULL,
   A varchar2(3) NOT NULL,
   B float(15),
   C float(15),
   D char(1) NOT NULL,
   CONSTRAINT tbl2_PK PRIMARY KEY (ID,A,D)
)
;

UPDATE 
  (select tbl1.b, tbl2.c 
   from tbl1 inner join tbl2 
   on tbl1.id=tbl2.id 
   and tbl1.a=tbl2.a 
   and tbl1.b=tbl2.b 
   and tbl1.a='foo' 
   and tbl2.D='a') 
set b=c;

我该如何定义我的select语句，使得Oracle能够确认我没有唯一性违规？

- Jim Kiley

Answer 1

你应该能够使用相关子查询来实现这个。

UPDATE tbl1 t1
   SET t1.b = (SELECT c
                 FROM tbl2 t2
                WHERE t1.id = t2.id
                  AND t1.a  = t2.a
                  AND t1.b  = t2.b
                  AND t2.d  = 'a')
 WHERE t1.a = 'foo'
   AND EXISTS( SELECT 1
                 FROM tbl2 t2
                WHERE t1.id = t2.id
                  AND t1.a  = t2.a
                  AND t1.b  = t2.b
                  AND t2.d  = 'a')

你所编写的UPDATE的问题在于，Oracle无法保证对于单个tbl1.b值只有一个tbl2.c值与之对应。如果tbl2中存在多行与tbl1中的某一行相对应，则关联更新将引发错误，指示单行子查询返回了多个行。在这种情况下，你需要在子查询中添加一些逻辑，以指定在该情况下使用tbl2的哪一行。

- Justin Cave

---

这似乎解决了问题 - 谢谢进一步的解释。这很有道理。 - Jim Kiley

Answer 2

这个语句会出现错误（ORA-01779无法修改映射到非关键保留表的列），因为它试图修改基本tbl1table，而tbl1表在视图中不是关键保留的。 因为虽然（ID，A）是dept表的一个键，但它不是join的一个键。

- 王奕然

---

链接已经不存在了，您能否更新或者总结一下正确页面的信息？ - Kosi2801

它仍然存在，但现在它是http://docs.oracle.com/cd/E11882_01/server.112/e25494/views.htm#ADMIN11783。它指的是：Oracle 11.2数据库管理员指南-> §24管理视图、序列和同义词->§§关键保留表。 - user1136452

Answer 3

根据（http://www.orafaq.com/tuningguide/updateable%20view.html），似乎您的视图不是保留键视图。实际上，您在非主键上进行连接，这似乎是不允许的。

- Abbadon