Oracle - 更新联接 - 非键保留表

发布于 2025-01-06 17:28:09 字数 765 浏览 3 评论 0原文

我正在尝试复制 Ingres“从 tbl2 更新 tbl1”命令，该命令在 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;

如何定义我的选择，以便 Oracle 满意我没有唯一性违规？

原文

I'm trying to replicate an Ingres "update tbl1 from tbl2" command, which doesn't exactly exist in Oracle.

So I use "update (select tbl1 join tbl2...)" command. Both tables have primary keys defined and I thought that my join was uniquely identifying rows, but I'm still getting "ORA-01779: cannot modify a column which maps to a non key-preserved table".

Here are suitably anonymized table definitions and the update I'm trying to execute:

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;

How can I define my select such that Oracle will be satisfied that I have no uniqueness violations?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

感情废物 2025-01-13 17:28:09

您应该能够使用相关子查询来完成此操作。

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 无法保证恰好有 1 个 tbl2.c 值对应于单个 tbl1.b 值。如果 tbl1 中的任何特定行在 tbl2 中存在多行，则相关更新将引发错误，指示单行子查询返回多行。在这种情况下，您需要向子查询添加一些逻辑，以指定在这种情况下使用 tbl2 中的哪一行。

You should be able to do this with a correlated subquery

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')

The problem with the UPDATE that you've written is that Oracle cannot guarantee that there is exactly 1 tbl2.c value that corresponds to a single tbl1.b value. If there are multiple rows in tbl2 for any particular row in tbl1, the correlated update is going to throw an error indicating that a single-row subquery returned multiple rows. In that case, you'd need to add some logic to the subquery to specify which row from tbl2 to use in that case.

回复收藏 0 原文