0

I have a application that uses bulk insert to put data into Oracle database table. Now I need to change it, so it upserts it, but have a hard time finding best solution to do this. All the sites suggest using MERGE to insert/update rows, as it's most effective and simplest solution, but all the examples base on data that is already in the table.

What would be the best solution for this change? Is using some kind of temporary or staging table necessary, or is there a way to skip this?

Paweł Halicki
  • 90
  • 7
  • "examples base on data that is already in the table" - you might be looking at bad examples, [merge will allow you to insert/update as needed](http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) – Dariusz May 09 '16 at 10:05

1 Answers1

2

MERGE can be used with constant values as well.

However due to Oracle's lack of support for the values() row constructor this gets a bit ugly:

merge into the_table
using (
  select 1 as id, 'arthur' as name from dual
) t on (t.id = the_table.id)
when matched then 
   update set name = t.name
when not matched then 
   insert (id, name)
   values (t.id, t.name);

This can also be used for multiple rows:

merge into the_table
using (
  select 1 as id, 'arthur' as name from dual -- first row
  union all
  select 2, 'ford' from dual -- second row
  union all
  select 3, 'zaphod' from dual -- third row
) t on (t.id = the_table.id)
when matched then 
   update set name = t.name
when not matched then 
   insert (id, name)
   values (t.id, t.name);