Oracle Update statement with an Inner Join
In Oracle, you can't use a from
clause in an update
statement that way. Any of the following should work.
UPDATE dSET 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_idWHERE d_user_id IS NULL;
However, my preference is to use MERGE
in this scenario:
MERGE INTO dUSING cON (d.mgr_cd = c.mgr_cd)WHEN MATCHED THEN UPDATE SET d.user_id = c.user_id WHERE d.user_id IS NULL;