SQL 在 Oracle 更新语句中使用子查询而不是表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/230196/
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
Using a subquery instead of a table name in an Oracle Update Statement
提问by NateSchneider
I need write an update statement that used multiple tables to determine which rows to update, since in Oracle, multiple tables aren't allowed. The following query will return a "ORA-00971: Missing SET keyword" error
我需要编写一个使用多个表来确定要更新哪些行的更新语句，因为在 Oracle 中，不允许使用多个表。以下查询将返回“ORA-00971：缺少 SET 关键字”错误
UPDATE
TABLE1 a,
TABLE2 b
SET
a.COL1 = 'VALUE'
WHERE
a.FK = b.PK
AND b.COL2 IN ('SET OF VALUES')
Looking up the UPDATE statement syntax on oracle, I found the following link, which shows that you can use a subquery in place of a table name.
在 oracle 上查找 UPDATE 语句语法，我找到了以下链接，它表明您可以使用子查询代替表名。
When I tried to write the query like this, I got a "ORA-01779: Cannot modify a column which maps to a non key-preserved table"
当我尝试编写这样的查询时，出现“ORA-01779：无法修改映射到非键保留表的列”
UPDATE
(
SELECT
a.COL1
FROM
TABLE1 a,
TABLE2 b
WHERE
a.FK = b.PK
AND b.COL2 IN ('SET OF VALUES')
) update_tbl
SET
update_tbl.COL1 = 'VALUE'
I did rewrite the query (show below) using an EXISTS statement instead and it works fine, but would still like to know how this is done.
我确实使用 EXISTS 语句重写了查询（如下所示）并且它工作正常，但仍然想知道这是如何完成的。
UPDATE
TABLE1 update_tbl
SET
update_tbl.COL1 = 'VALUE'
WHERE
EXISTS (
SELECT
1
FROM
TABLE1 a
TABLE2 b
WHERE
a.FK = b.PK
AND b.COL2 IN ('SET OF VALUES')
AND update_tbl.PK = a.PK
)
Thanks! -Nate
谢谢！-内特
采纳答案by Tony Andrews
Another option:
另外一个选项：
UPDATE TABLE1 a
SET a.COL1 = 'VALUE'
WHERE a.FK IN
( SELECT b.PK FROM TABLE2 b
WHERE b.COL2 IN ('SET OF VALUES')
)
Your second example would work if (a) the view included the declaredPK of TABLE1:
如果 (a) 视图包含TABLE1的声明PK，则您的第二个示例将起作用：
UPDATE
(
SELECT
a.COL1, a.PKCOL
FROM
TABLE1 a,
TABLE2 b
WHERE
a.FK = b.PK
AND b.COL2 IN ('SET OF VALUES')
) update_tbl
SET
update_tbl.COL1 = 'VALUE'
... and (b) TABLE1.FK was a declaredforeign key to TABLE2
...和 (b) TABLE1.FK 是TABLE2的声明外键
(By declared I mean that a constraint exists and is enabled).
（声明是指存在约束并已启用）。
回答by Nick Pierpoint
I find that a nice, quick, consistent way to turn a SELECT statement into an UPDATE is to make the update based on the ROWID.
我发现将 SELECT 语句转换为 UPDATE 的一种不错的、快速的、一致的方法是根据 ROWID 进行更新。
UPDATE
TABLE1
SET
COL1 = 'VALUE'
WHERE
ROWID in
(
SELECT
a.rowid
FROM
TABLE1 a,
TABLE2 b
WHERE
a.FK = b.PK
AND b.COL2 IN ('SET OF VALUES')
)
So, your inner query is defining the rows to update.
因此，您的内部查询正在定义要更新的行。
回答by JosephStyons
The syntax of your example is fine, but Oracle requires that the subquery include primary keys. That's a pretty significant limitation.
您示例的语法很好，但 Oracle 要求子查询包含主键。这是一个非常重要的限制。
On a related note, you can also use parentheses to use 2 or more fields in an IN statement, as in:
在相关说明中，您还可以使用括号在 IN 语句中使用 2 个或更多字段，如下所示：
UPDATE
TABLE1 update_tbl
SET
update_tbl.COL1 = 'VALUE'
WHERE
(update_tbl.PK1, update_tbl.pk2) in(
select some_field1, some_field2
from some_table st
where st.some_fields = 'some conditions'
);
回答by David Aldridge
When you perform an update you can obviously only tell the system to update the value to a single new value -- telling it to update "X" to both "Y" and "Z" doesn't make sense. So, when you base an update on the result of an inline view Oracle performs a check that there are sufficient constraints in place to prevent a modified column being potentially updated twice.
当您执行更新时，您显然只能告诉系统将值更新为单个新值——告诉系统将“X”更新为“Y”和“Z”是没有意义的。因此，当您基于内联视图的结果进行更新时，Oracle 会检查是否有足够的约束来防止修改的列可能被更新两次。
In your case I expect that TABLE2.PK is not actually a declared primary key. If you place a primary or unique constraint on that columnthen you'd be good to go.
在您的情况下，我希望 TABLE2.PK 实际上不是声明的主键。如果您在该列上放置主要或唯一约束，那么您就可以开始了。
There is an undocumented hint to byass the update join cardinality check, used internally by Oracle, but I wouldn't advise using it.
有一个未记录的提示绕过更新连接基数检查，由 Oracle 内部使用，但我不建议使用它。
One workaround for this is to use a MERGE statement, which is not subject to the same test.
一种解决方法是使用 MERGE 语句，该语句不受相同测试的约束。
回答by Franck
I found what i needed here:
Useful SQL Commands
I needed to update one table with the result of a join
I tried the above solutions without success :(
我在这里找到了我需要的东西：
有用的 SQL 命令
我需要用连接的结果更新一个表
我尝试了上述解决方案但没有成功:(
Here is an extract of the page i pointed above
Using cursors i was able to achieve the task successfully
I'm sure there's another solution but this one worked so...
这是我在上面指出的页面的摘录
使用游标我能够成功完成任务
我确定还有另一种解决方案，但这个解决方案如此有效......
DECLARE
/* Output variables to hold the result of the query: */
a T1.e%TYPE;
b T2.f%TYPE;
c T2.g%TYPE;
/* Cursor declaration: */
CURSOR T1Cursor IS
SELECT T1.e, T2.f, T2.g
FROM T1, T2
WHERE T1.id = T2.id AND T1.e <> T2.f
FOR UPDATE;
BEGIN
OPEN T1Cursor;
LOOP
/* Retrieve each row of the result of the above query
into PL/SQL variables: */
FETCH T1Cursor INTO a, b;
/* If there are no more rows to fetch, exit the loop: */
EXIT WHEN T1Cursor%NOTFOUND;
/* Delete the current tuple: */
DELETE FROM T1 WHERE CURRENT OF T1Cursor;
/* Insert the reverse tuple: */
INSERT INTO T1 VALUES(b, a);
/* Here is my stuff using the variables to update my table */
UPDATE T2
SET T2.f = a
WHERE T2.id = c;
END LOOP;
/* Free cursor used by the query. */
CLOSE T1Cursor;
END;
.
run;
Note: Don't forget to commit ;-)
注意：不要忘记提交 ;-)
回答by Chris Ammerman
Each row in the result set of the query in your UPDATE clause must map back to one and only one row in the table you are trying to update, and in a way that Oracle can follow automatically. Since the query is really a view, one way to think about it is that Oracle needs to be able to join the view back to the target table, in order to know what row to update.
UPDATE 子句中查询结果集中的每一行都必须映射回您尝试更新的表中的一行且仅一行，并且 Oracle 可以自动跟踪。由于查询实际上是一个视图，一种思考方式是 Oracle 需要能够将视图连接回目标表，以便知道要更新哪一行。
This essentially means that you need to include the primary key of the destination table in that query. You might be able to use some other unique index field(s) too, but I can't guarantee the Oracle DBMS is smart enough to allow that.
这实质上意味着您需要在该查询中包含目标表的主键。您也可以使用其他一些唯一索引字段，但我不能保证 Oracle DBMS 足够智能以允许这样做。
