MERGE uses rowtype variable in PL / SQL on Oracle?

Question

With a bar variable of type foo%ROWTYPE I can do both INSERT and UPDATE in PL / SQL:

 INSERT INTO foo VALUES bar; 

 UPDATE foo SET ROW = bar WHERE id = bar.id; 

But how do I make MERGE ? The following approach generates the error message below:

 MERGE INTO foo USING bar ON foo.id = bar.id WHEN MATCHED THEN UPDATE SET ROW = bar WHEN NOT MATCHED THEN INSERT VALUES bar; 

PL / SQL: ORA-00942: table or view does not exist

Answer 1

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 be updated: foo.id) suggests that you are trying to do something similar to the following:

 merge into foo using (select null from dual) on (foo.id = bar.id) when matched then update set foo.id = bar.id, foo.another_field = bar.another_field when not matched then insert VALUES bar; 

As indicated in the error message, the columns specified in the "ON" clause cannot be updated. So the following will work fine:

 merge into foo using (select null from dual) on (foo.id = bar.id) when matched then update set foo.another_field = bar.another_field when not matched then insert VALUES bar; 

If you really need to update foo.id, there is a possible solution here: How to avoid ORA-3814 error when merging?

Edit

A possible alternative would be the following:

 update foo set row = bar where foo.id = bar.id; if sql%rowcount = 0 then insert into foo values bar; end if; 

This should essentially amount to performing the same action as the merge operator.