[ACCEPTED]-Oracle merge constants into single table-merge
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 ( 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);
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:
begin insert into data (...) values (...); exception when dup_val_on_index then update data set ... where ...; end;
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