SQL How can I avoid ora-38104?

ora-30926: unable to get a stable set of rows in the source tables
oracle merge
select and update in one query oracle
oracle merge delete
oracle update with join
oracle merge into same table
merge with where clause
pl sql update multiple rows in the target table from one row in the source table

I want to update big table TEMP_MA_CONTACT and I use MERGE INTO:

MERGE INTO TEMP_MA_CONTACT C
USING (select * from TABLE_TO_CHANGE_2601) T
ON (C.CUSTOMER_RK = T.CUSTOMER_RK)
WHEN MATCHED THEN UPDATE SET C.CUSTOMER_RK = T.NEW_CUSTOMER_RK

but Oracle says:

ORA-38104: Columns referenced in the ON Clause cannot be updated

Mmmm, I'm not sure if it is possible with merge.. but you can always use update with a subquery:

UPDATE TEMP_MA_CONTACT C
set c.customer_rk =
(select P.new_customer_rk from table_to_change_2601 p where P.customer_rk = C.customer_rk)

Another idea is this:

ALTER TABLE TEMP_MA_CONTACT add TEMP_COL NUMBER(5);
UPDATE TEMP_MA_CONTACT set TEMP_COL = customer_rk;

and then use your original merge, but on this column like this:

MERGE INTO TEMP_MA_CONTACT C
USING (select * from TABLE_TO_CHANGE_2601) T
ON (C.TEMP_COLUMN = T.CUSTOMER_RK)
WHEN MATCHED THEN UPDATE SET C.CUSTOMER_RK = T.NEW_CUSTOMER_RK;
commit;

and then droping the column

ALTER TABLE TEMP_MA_CONTACT drop column TEMP_COLUMN

How to Work Around ORA-38104: Columns referenced in the ON , How to Work Around ORA-38104: Columns referenced in the ON Clause cannot be updated. Behind the scenes, MySQL will check all unique constraints for duplicates and reject the insert, replacing it by the update statement instead. SQL-Fehler: ORA-38104: Columns referenced in the ON Clause cannot be updated: “T”.”USER_NAME” Obviously, this is some protection against the situation where such an update would suddenly move a row from the matched to the not matched group.


The best variant to resolve this problem is:

1) First add rowid to table_to_change_2601

    CREATE TABLE table_to_change_2601_new AS 
SELECT T.*,
I.ROWID AS ROW_ID FROM
        table_to_change_2601 T JOIN temp_ma_contact I ON T.CUSTOMER_RK = I.CUSTOMER_RK

2) Merge on this rowid's

MERGE INTO temp_ma_contact C
USING (select * from table_to_change_2601_new) T
ON (C.ROWID = T.ROW_ID)
WHEN MATCHED THEN UPDATE SET C.CUSTOMER_RK = T.NEW_CUSTOMER_RK

SQL & PL/SQL » Avoid ORA-38104 - olumns , Avoid ORA-38104 - olumns referenced in the ON Clause cannot be The final result would be, update columns c1,c2,c3 from table tab_1  Avoid ORA-38104 - olumns referenced in the ON Clause cannot be updated Oracle 11g Release 2 OraFAQ Forum: SQL & PL/SQL » Avoid ORA-38104 - olumns referenced in the ON Clause cannot be updated Members


There are a few workarounds that I have described in this blog post here. One of the workarounds that seems to preserve the performance characteristics of the original query (allowing index usage on CUSTOMER_RK columns) is this one, using row value expressions:

MERGE INTO TEMP_MA_CONTACT C
USING (select * from TABLE_TO_CHANGE_2601) T
ON ((C.CUSTOMER_RK, 'dummy') = ((T.CUSTOMER_RK, 'dummy')))
WHEN MATCHED THEN UPDATE SET C.CUSTOMER_RK = T.NEW_CUSTOMER_RK

This seems to work up until Oracle 18c

ORA-38104: Columns referenced in the ON Clause , It would help if you would add the MERGE statement, to see what Tom was but implementationally it could be difficult, especially as the SQL  SQL & PL/SQL :: Avoid ORA-38104 - Columns Referenced In The ON Clause Cannot Be Updated May 9, 2012. this is my test data : create table tab_1(c1 number,c2 number,c3 number); create table tab_2(c1 number,c2 number,c3 number,c4 number,c5 number,c6 number); insert into tab_1 values(1,1,1); insert into tab_1 values(2,2,2);


ORA-38029 to ORA-39962, ORA-38104: Columns referenced in the ON Clause cannot be updated: string Note that repeatedly causing this error can make it stop happening without Cause: An unexpected error occurred while executing recursive SQL to insert a row  On Sql-Server it works great, but Oracle says: ORA-38104: Columns referenced in the ON Clause cannot be updated: TARGET.ISDELETED If there is a matching record with IDELETED = 0, I want the primary key violation as an exception, that's why I can't move "TARGET.ISDELETED = 1" from the on-clause to the update-statement.


[PDF] MERGE SQL Statement: Lesser Known Facets, MERGE is a part of SQL 2003 and has been ORA-30926 is definitely the most confusing error related to MERGE Find the correct way to avoid duplicates. ORA-38104: Columns referenced in the ON Clause cannot be updated: "T". While SQL Injection can affect any data-driven application that uses a SQL database, it is most often used to attack web sites. SQL Injection is a code injection technique that hackers can use to insert malicious SQL statements into input fields for execution by the underlying SQL database.


MERGE uses rowtype variable in PL / SQL on Oracle?, MichaelS answer in the topic mentioned above should work fine. The error message you receive (ORA-38104: The columns indicated in the ON section cannot  Hi, I am facing performance for the below update statement. It is taking close to 30 minustes. Any idea how can we tune this? I have 2 more similar update statement and overall the job is running for more than 1 hr. Update UPDATE table1 t1 SET col2 = (SELECT t2.col2 FROM table2 t2, table3 t3 WHERE t3.col1 = t2.col1 AND t3.col2 = '0' AND t3.col3 ='CL' AND t3.col4 IS NOT NULL AND t3.col5 IS NOT