CODEWITHSUNDEEP
oracle-databaseplsqlsql-merge
Anders
Anders

Reputation: 8578

MERGE using a rowtype variable in PL/SQL on Oracle?

With a variable bar of the 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 do a 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

Upvotes: 8

Views: 9547

Answers (1)

Chrisrs2292
Chrisrs2292

Reputation: 1094

The answer MichaelS gives in the thread mentioned above should work fine. The error message you're receiving (ORA-38104: Columns referenced in the ON Clause cannot be updated: foo.id) suggests you're 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 the error states, columns referenced in the "ON" clause cannot be updated. As such, the following would 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 on merge?

Edit

A possible alternative would be to do 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 equate to doing the same thing as the merge statement.

Upvotes: 5

Related Questions