Oracle Update statement with an Inner Join

Question

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?

Answer 1

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;