[ACCEPTED]-Oracle merge constants into single table-merge
Accepted answer
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.
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.