[ACCEPTED]-Oracle merge constants into single table-merge

Accepted answer
Score: 24

I don't consider using dual to be a hack. To 2 get rid of binding/typing twice, I would 1 do something like:

merge into data
using (
        'someid' id,
        'testKey' key,
        'someValue' value
) val on (
    and data.key=val.key
when matched then 
    update set data.value = val.value 
when not matched then 
    insert (id, key, value) values (val.id, val.key, val.value);
Score: 5

I would hide the MERGE inside a PL/SQL API 2 and then call that via JDBC:

data_pkg.merge_data ('someid', 'testKey', 'someValue');

As an alternative 1 to MERGE, the API could do:

   insert into data (...) values (...);
   when dup_val_on_index then
      update data
      set ...
      where ...;
Score: 2

I prefer to try the update before the insert 6 to save having to check for an exception.

update data set ...=... where ...=...;

if sql%notfound then

    insert into data (...) values (...);

end if;

Even 5 now we have the merge statement, I still 4 tend to do single-row updates this way - just 3 seems more a more natural syntax. Of course, merge really 2 comes into its own when dealing with larger 1 data sets.

More Related questions