8

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

Anders
  • 8,307
  • 9
  • 56
  • 88
  • 2
    believe this has been answered on the oracle forums here https://community.oracle.com/thread/621511?start=0&tstart=0 look at Solomon's answer. There is also a FORALL Merge that might work for you. http://www.morganslibrary.org/reference/plsql/array_processing.html – Bryan Dellinger Nov 16 '15 at 18:13
  • Thank you for your reply. Solomons answer is enumerating all the fields of the rowtype, which sort of takes away the whole point of using a rowtype. I'm not sure I understand how a `FORALL MERGE` would help here. The solution from MichaelS looks promising, though. – Anders Nov 17 '15 at 08:41
  • Nope, cant get MichaelS answer to work either. I just get `ORA-38104: Columns referenced in the ON Clause cannot be updated: foo.id` – Anders Nov 17 '15 at 08:46
  • It might help if you give as a more lifelike example. You're inserting/updating "foo". But what is "bar" ? a table with the same structure? Or just a rowtype you previously defined? – Non Plus Ultra Nov 30 '15 at 16:56
  • @NonPlusUltra As the question says, `bar` is a variable of the type `foo%ROWTYPE`. `foo` is a table. – Anders Dec 02 '15 at 08:35

1 Answers1

5

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.

Community
  • 1
  • 1
Chrisrs2292
  • 1,094
  • 1
  • 12
  • 23
  • Unless you really need to use a merge statement for some reason, I have added a possible alternative solution for you. As stated by Solomon Yakobson (https://community.oracle.com/thread/621511?start=0&tstart=0) you cannot use a rowtype in the when matched clause of a merge statement – Chrisrs2292 Dec 16 '15 at 09:51
  • in this case - generally what is faster option 1 - merge or the option 2 - update and then insert using IF – Ashish Shetkar Oct 26 '18 at 10:56
  • 1
    Personally not sure, but according to the answer over here, the merge: https://dba.stackexchange.com/questions/16028/oracles-merge-v-select-update-insert – Chrisrs2292 Oct 26 '18 at 11:02