Oracle merge constants into single table

Question

In Oracle, given a simple data table:

create table data (
    id       VARCHAR2(255),
    key      VARCHAR2(255),
    value    VARCHAR2(511));

suppose I want to "insert or update" a value. I have something like:

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

Is there a better way than this? This command seems to have the following drawbacks:

• Every literal needs to be typed twice (or added twice via parameter setting)
• The "using dual" syntax seems hacky

If this is the best way, is there any way around having to set each parameter twice in JDBC?

Answer 1

Use a stored procedure

Answer 2

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

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

As an alternative to MERGE, the API could do:

begin
   insert into data (...) values (...);
exception
   when dup_val_on_index then
      update data
      set ...
      where ...;
end;

Answer 3

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

merge into data
using (
    select
     'someid' id,
     'testKey' key,
     'someValue' value
    from
     dual
) val on (
    data.id=val.id
    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);

Answer 4

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

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

if sql%notfound then

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

end if;

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

Answer 5

When your source and target table are same,you need to use DUAL.