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

2021-12-17 00:00:00 join sql-update sql oracle

我正在尝试复制 Ingres从 tbl2 更新 tbl1"命令，该命令在 Oracle 中并不完全存在.

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

所以我使用更新(选择 tbl1 加入 tbl2...)"命令.两个表都定义了主键，我认为我的连接是唯一标识行，但我仍然收到ORA-01779:无法修改映射到非键保留表的列".

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;

如何定义我的选择，以便 Oracle 对我没有违反唯一性感到满意?

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

推荐答案

您应该能够使用相关子查询来做到这一点

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

您编写的 UPDATE 的问题在于 Oracle 无法保证有 1 个 tbl2.c 值对应于单个 tbl1.b 值.如果tbl2 中有多个行用于tbl1 中的任何特定行，则相关更新将抛出一个错误，表明单行子查询返回了多行.在这种情况下，您需要向子查询添加一些逻辑，以指定在这种情况下使用 tbl2 中的哪一行.

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.

相关文章