具有内部联接的Oracle Update语句 [英] Oracle Update statement with an Inner Join
本文介绍了具有内部联接的Oracle Update语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用内部联接编写一个简单的更新语句,但是我在SQL Server中执行此操作的方式似乎在ORACLE中不起作用.这是更新:
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:
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;
似乎我得到的错误是在FROM
上.有人可以解释一下这是什么原因以及如何解决它吗?
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?
推荐答案
In Oracle, you can't use a from
clause in an update
statement that way. Any of the following should work.
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;
但是,我更喜欢在这种情况下使用MERGE
:
However, my preference is to use MERGE
in this scenario:
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;
这篇关于具有内部联接的Oracle Update语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文