ORA-38104: Columns referenced in the ON Clause cannot be updated: "U"."USER_ID"

在用merge语句更新一个表的时候报错：

SQL> merge into OUTSITE_USERINFO u
  2      using tmp_update_newlib ut
  3      on (u.user_id = ut.NEWCOURSE_ID)
  4      when matched then
  5      update set u.user_id = ut.NEWLIB_ID;
    on (u.user_id = ut.NEWCOURSE_ID)
        *
ERROR at line 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "U"."USER_ID"

看错误意思大概是不能更新on条件引用的列

查看官方文档，确实如此：

merge_update_clause

Restrictions on the merge_update_clause This clause is subject to the following restrictions:

• You cannot update a column that is referenced in the ON condition clause.

这种情况可以用update语句去更新

SQL> update OUTSITE_USERINFO set user_id=(select NEWLIB_ID from tmp_update_newlib where OUTSITE_USERINFO.user_id=tmp_update_newlib.N
EWCOURSE_ID)
  2  where user_id in (select OUTSITE_USERINFO.user_id from OUTSITE_USERINFO,tmp_update_newlib where OUTSITE_USERINFO.user_id=tmp_up
date_newlib.NEWCOURSE_ID);


1 row updated.

记录一下。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1155917/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1155917/