SQL Oracle - 更新连接 - 非键保留表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9335536/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - update join - non key-preserved table
提问by Jim Kiley
I'm trying to replicate an Ingres "update tbl1 from tbl2" command, which doesn't exactly exist in Oracle.
我正在尝试复制 Ingres“从 tbl2 更新 tbl1”命令，该命令在 Oracle 中并不完全存在。
So I use "update (select tbl1 join tbl2...)" command. Both tables have primary keys defined and I thoughtthat 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".
所以我使用“更新（选择 tbl1 加入 tbl2...）”命令。两个表都定义了主键，我认为我的连接是唯一标识行，但我仍然收到“ORA-01779：无法修改映射到非键保留表的列”。
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?
我如何定义我的选择，以便 Oracle 对我没有唯一性违规感到满意？
回答by Justin Cave
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 UPDATEthat you've written is that Oracle cannot guarantee that there is exactly 1 tbl2.cvalue that corresponds to a single tbl1.bvalue. If there are multiple rows in tbl2for 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 tbl2to use in that case.
UPDATE您编写的 的问题在于 Oracle 无法保证恰好有 1 个tbl2.c值对应于单个tbl1.b值。如果 intbl2中的任何特定行有多行tbl1，相关更新将抛出一个错误，指示单行子查询返回多行。在这种情况下，您需要向子查询添加一些逻辑以指定tbl2在这种情况下要使用的行。
回答by Amitābha
This statement fails with an error (ORA-01779 cannot modify a column which maps to a non key-preserved table), because it attempts to modify the base tbl1table, and the tbl1 table is not key-preserved in the view . because although (ID,A) is a key of the dept table, it is not a key of the join.
此语句失败并显示错误（ORA-01779 无法修改映射到非键保留表的列），因为它尝试修改基本 tbl1table，而 tbl1 表在视图中不是键保留的。因为虽然 (ID,A) 是 dept 表的键，但它不是连接的键。
回答by Abbadon
It seems your view is not a key-preserved view according to (http://www.orafaq.com/tuningguide/updateable%20view.html). Indeed you make your join on not primary key which seems to be not allowed.
根据 ( http://www.orafaq.com/tuningguide/updateable%20view.html)，您的视图似乎不是保留键的视图。实际上，您在似乎不允许的主键上进行了连接。
