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?

Solution 1

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);

Solution 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;

Solution 3

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.

Comments

[David Aldridge]

I think you're right that it seems a more natural syntax, but I prefer the single-transaction approach of the merge myself -- no chance of anything untoward happening between update and insert.

[Nick Pierpoint]

Nothing untoward can happen between the update and the insert - this is an Oracle consistent transaction view.

[Mike Meyers]

I know this is quite an old thread, but I've recently used this method and it is NOT safe to use with concurrent transactions. It is possible for two sessions to try the update the same row at the same time, find there is nothing there then both try to insert. Safer to do the insert first then catch the exception. I accept that this is unlikely but can happen. I was using DBMS_JOB to set off two parallel processes at the same time.