在用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博客

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

最新文章