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