Problem
Depending on whether the table already includes a row that matches the data, the UPSERT action either updates or inserts a record:
if table t has a row exists that has key X:
update t set mystuff... where mykey=X
else
insert into t mystuff...
What’s the best approach to handle this because Oracle doesn’t have a particular UPSERT statement?
Asked by Mark Harrison
Solution #1
The MERGE command joins two tables together. We may utilize this command by using DUAL. It’s worth noting that this isn’t protected from concurrent access.
create or replace
procedure ups(xa number)
as
begin
merge into mergetest m using dual on (a = xa)
when not matched then insert (a,b) values (xa,1)
when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;
A B
---------------------- ----------------------
10 2
20 1
Answered by Mark Harrison
Solution #2
The PL/SQL example above was wonderful since I wanted to do something similar on the client side…so here’s the SQL I used to deliver a similar statement directly from some C# code.
MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name")
VALUES ( 2097153,"smith", "john" )
However, from a C# perspective, this appears to be slower than performing the update and then checking to see if the number of rows affected is zero before inserting.
Answered by MyDeveloperDay
Solution #3
An alternative to MERGE (the “traditional” method):
begin
insert into t (mykey, mystuff)
values ('X', 123);
exception
when dup_val_on_index then
update t
set mystuff = 123
where mykey = 'X';
end;
Answered by Tony Andrews
Solution #4
Another alternative without the exception check:
UPDATE tablename
SET val1 = in_val1,
val2 = in_val2
WHERE val3 = in_val3;
IF ( sql%rowcount = 0 )
THEN
INSERT INTO tablename
VALUES (in_val1, in_val2, in_val3);
END IF;
Answered by Brian Schmitt
Solution #5
INSERT INTO mytable (id1, t1)
SELECT 11, 'x1' FROM DUAL
WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11);
UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;
Answered by test1
Post is based on https://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table