Oracle-更新联接-非键保留表 [英] Oracle - update join - non key-preserved table
问题描述
我正在尝试复制一个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无法保证与单个tbl1.b
值完全对应的1个tbl2.c
值.如果对于tbl1
中的任何特定行,在tbl2
中存在多行,则相关更新将引发错误,指示单行子查询返回了多行.在这种情况下,您需要向子查询添加一些逻辑,以指定在这种情况下要使用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.
这篇关于Oracle-更新联接-非键保留表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!