Oracle内连接的更新语句
Oracle中,在update
语句中不能像上面那样使用from
子句。以下任何一种方法都可以解决这个问题。
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;