带有内部联接的 Oracle Update 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7664312/
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 statement with an Inner Join
提问by esastincy
I am trying to write a simple update statement with an inner join, but the way I would do this in SQL server does not seem to be working in ORACLE. Here is the Update:
我正在尝试使用内部联接编写一个简单的更新语句，但是我在 SQL Server 中执行此操作的方式似乎不适用于 ORACLE。这是更新：
UPDATE D
SET D.USER_ID = C.USER_ID
FROM D INNER JOIN C
ON D.MGR_CD = C.MGR_CD WHERE D.USER_ID IS NULL;
It seems like the error I am getting is on the FROM. Can someone explain to meet what the cause of this is and how to work around it?
我遇到的错误似乎在FROM. 有人可以解释一下这是什么原因以及如何解决它吗？
回答by Allan
In Oracle, you can't use a fromclause in an updatestatementthat way. Any of the following should work.
在 Oracle 中，您不能以这种方式from在update语句中使用子句。以下任何一项都应该有效。
UPDATE d
SET d.user_id =
(SELECT c.user_id
FROM c
WHERE d.mgr_cd = c.mgr_cd)
WHERE d.user_id IS NULL;
UPDATE (SELECT d.user_id AS d_user_id, c.user_id AS c_user_id
FROM d INNER JOIN c ON d.mgr_cd = c.mgr_cd
WHERE d.user_id IS NULL)
SET d_user_id = c_user_id;
UPDATE (SELECT d.user_id AS d_user_id, c.user_id AS c_user_id
FROM d INNER JOIN c ON d.mgr_cd = c.mgr_cd)
SET d_user_id = c_user_id
WHERE d_user_id IS NULL;
However, my preference is to use MERGEin this scenario:
但是，我更喜欢MERGE在这种情况下使用：
MERGE INTO d
USING c
ON (d.mgr_cd = c.mgr_cd)
WHEN MATCHED THEN
UPDATE SET d.user_id = c.user_id
WHERE d.user_id IS NULL;
