Oracle: how to UPSERT (update or insert into a table?)

The MERGE statement merges data between two tables. Using DUALallows us to use this command. Note that this is not protected against concurrent access.

create or replaceprocedure ups(xa number)asbegin    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                     220                     1

The dual example above which is in PL/SQL was great becuase I wanted to do something similar, but I wanted it client side...so here is the SQL I used to send a similar statement direct from some C#

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 provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was.

An alternative to MERGE (the "old fashioned way"):

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;