<p>The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:</p>

<pre><code>if table t has a row exists that has key X:
    update t set mystuff... where mykey=X
else
    insert into t mystuff...
</code></pre>

<p>Since Oracle doesn't have a specific UPSERT statement, what's the best way to do this?</p>

The
MERGE statement
merges data between two tables.  Using DUAL
allows us to use this command.  Note that this is not protected against 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
```
```