在用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.
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.
记录一下。