带有内连接的 Oracle Update 语句

发布于 2024-12-08 08:46:58 字数 267 浏览 0 评论 0原文

我正在尝试编写一个带有内部联接的简单更新语句，但是我在 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;

我收到的错误似乎是在 FROM 上。有人可以解释一下这是什么原因以及如何解决它吗？

原文

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;

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

逆光下的微笑 2024-12-15 08:46:58

在 Oracle 中，不能在 更新语句这样。以下任何一项都应该有效。

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：

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;

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;

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;

回复收藏 0 原文

~没有更多了~