What is cause of ORA-38104 error on SQL merge?

Question

I have a code like this

MERGE INTO target_table tgt
USING source_table src
on(tgt.c1=src.c1)
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2

I get ORA-38104: Columns referenced in the ON clause cannot be updated. I understand the reason for this error. But how can we rewrite this code? Is there any possibilities without using cursor?

score 11 · Accepted Answer · answered Oct 25 '11 at 11:47

11

How about this, the outer join means the rid will be null and thus fail, and so flow into the WHEN NOT MATCHED part of the statement if you have one

MERGE INTO target_table tgt
USING ( SELECT t2.ROWID AS rid
            ,  s2.c2
        FROM   target_table t2
             , source_table s2
        WHERE t2.c1 (+) = s2.c1
      ) src
ON (tgt.rowid = src.rid)
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2

answered Oct 25 '11 at 11:47

Sodved

8,428
2
31
43

I'm getting this error `ORA-01445-cannot select ROWID from a join view without a key-preserved table Cause: A SELECT statement attempted to select ROWIDs from a view derived from a join operation. Because the rows selected in the view do not correspond to underlying physical records, no ROWIDs can be returned. ` if i use this query – Vivek Nov 03 '11 at 09:37
1

You must be doing something slightly different. Are you sure you have `t2.ROWID AS something`? It would NOT be valid to use `src.ROWID` which is why I aliased it `AS rid` – Sodved Nov 03 '11 at 14:56
Yes, you are correct. I missed to rename `t2.ROWID` as `rid`. Its working fine after i add the alias name. Thank you Sodved :) – Vivek Nov 08 '11 at 07:05
1

This was great but , It was working for me on updates but did not insert any rows when i try with 'when not matched then ' statement. Please help me out. also i tried with insert several columns by using src.name likewise for the columns. still it says 0 rows merged even it was not matched. Would be a great help if i can get some help in here. – Sam Sep 10 '16 at 06:20

score 0 · Answer 2 · answered Jan 02 '19 at 16:06

You can exploit some workarounds for ORA-38104, which seem to work up until Oracle 18c, including this one, where you'd wrap your columns in a row value expression that contains an additional dummy expression:

MERGE INTO target_table tgt
USING source_table src
ON ((tgt.c1, 'dummy') = ((src.c1, 'dummy')))
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2

What is cause of ORA-38104 error on SQL merge?

2 Answers2

Linked

Related