SQL错误:ORA-38104:ON子句中引用的列无法更新

Question

MERGE /*+ GATHER_PLAN_STATISTICS*/
INTO ATM_REQUEST ATM
USING ATM_STATUS_VIEW ST
ON (    ATM.accountno = ST.accountno
AND atm.status IS NULL
AND atm.remarks IS NULL)
WHEN MATCHED
THEN
UPDATE SET ATM.STATUS = ST.STATUS, ATM.REMARKS = ST.REMARKS;

我犯了以下错误，有人能帮我吗。

Error at Command Line : 3 Column : 35
Error report -
SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "ATM"."STATUS"

Answer 1

您在ON子句中使用了列atm.status，因此无法更改UPDATE子句。您可以将IS NULL条件移动到UPDATE子句中。

MERGE /*+ GATHER_PLAN_STATISTICS*/
INTO atm_request atm
USING atm_status_view st
ON (atm.accountno = st.accountno)
WHEN MATCHED
THEN
UPDATE SET atm.status = st.status, atm.remarks = st.remarks
WHERE     atm.status IS NULL
AND atm.remarks IS NULL;

Answer 2

正如它所说，您不能更新ON子句中引用的列。

也许你想这么做：

MERGE /*+ GATHER_PLAN_STATISTICS*/
INTO ATM_REQUEST ATM
USING ATM_STATUS_VIEW ST
ON (ATM.accountno = ST.accountno)
WHEN MATCHED
THEN
UPDATE SET ATM.STATUS = ST.STATUS, ATM.REMARKS = ST.REMARKS
WHERE     atm.status IS NULL
AND atm.remarks IS NULL