Oracle MERGE: when to use it, what it does better, and the traps nobody mentions

A lot of PL/SQL I read does an “insert or update” like this:

update products set price = :price where product_id = :id;
if sql%rowcount = 0 then
  insert into products (product_id, price) values (:id, :price);
end if;

It works. But Oracle has one statement for exactly this job, MERGE, and many developers have never used it. MERGE compares a source (a table, a query, a JSON document, a single row of page items) with a target table, then updates the rows that already exist and inserts the ones that don’t. It can also skip rows that haven’t changed, and delete rows, in the same statement.

This guide shows when I reach for it, what it does better than the update-then-insert pattern, and the traps I ran into.

Every statement and every result below was run on Oracle AI Database 26ai (23.26), the Free edition on my own machine. MERGE itself is much older (Oracle 9i), and the DELETE clause and the one-branch-only form came in 10g, but I only ran these tests on 26ai.

The example#

A product table, and a feed that arrives from somewhere else (a supplier file, another system, a staging table):

create table products (
  product_id number primary key,
  name       varchar2(50),
  price      number(10,2),
  updated_at date);

create table product_feed (
  product_id number,
  name       varchar2(50),
  price      number(10,2),
  action     varchar2(1));   -- 'U' = add/update, 'D' = discontinued

insert into products values (1, 'Keyboard', 25,  date '2026-01-01');
insert into products values (2, 'Mouse',    10,  date '2026-01-01');
insert into products values (3, 'Monitor', 150,  date '2026-01-01');

insert into product_feed values (1, 'Keyboard',  27,  'U');   -- price changed
insert into product_feed values (2, 'Mouse',     10,  'U');   -- nothing changed
insert into product_feed values (3, 'Monitor',  150,  'D');   -- discontinued
insert into product_feed values (4, 'Webcam',    40,  'U');   -- new
insert into product_feed values (5, 'Old cable',  2,  'D');   -- new, but discontinued

The basic MERGE#

merge into products t                      -- the TARGET: the table that changes
using product_feed s                       -- the SOURCE: where the new data comes from
   on (t.product_id = s.product_id)        -- how a source row finds its target row
 when matched then                         -- the row exists: update it
   update set t.name       = s.name,
              t.price      = s.price,
              t.updated_at = sysdate
 when not matched then                     -- the row doesn't exist: insert it
   insert (product_id, name, price, updated_at)
   values (s.product_id, s.name, s.price, sysdate);

SQL%ROWCOUNT after it was 5: products 1, 2 and 3 were updated, and 4 and 5 were inserted. Read the parts in this order and it stays simple:

• ON decides, for every source row, matched or not matched. Put the key columns here, usually the primary key.
• WHEN MATCHED runs for the source rows that found a target row.
• WHEN NOT MATCHED runs for the source rows that didn’t.

Two things in that result aren’t what we want yet. The Mouse row was “updated” although nothing about it changed: its updated_at moved to today. And the discontinued Old cable was inserted. Both are fixed by adding a WHERE to a branch.

Update only the rows that really changed#

Each branch can take its own WHERE:

merge into products t
using product_feed s
   on (t.product_id = s.product_id)
 when matched then
   update set t.name = s.name, t.price = s.price, t.updated_at = sysdate
   where decode(t.name,  s.name,  0, 1) = 1
      or decode(t.price, s.price, 0, 1) = 1
 when not matched then
   insert (product_id, name, price, updated_at)
   values (s.product_id, s.name, s.price, sysdate)
   where s.action <> 'D';

This time SQL%ROWCOUNT was 2: only the Keyboard was updated (its price changed) and only the Webcam was inserted. The Mouse kept its old updated_at, and the Old cable was never inserted.

Why decode and not t.price <> s.price? Because <> is never true when one side is NULL. A product whose price goes from NULL to 30 would never be updated. decode treats two NULLs as equal and a NULL against a value as different, so decode(a, b, 0, 1) = 1 means “a and b are different, NULLs included”.

Skipping unchanged rows is worth the extra lines. A row you don’t update keeps its real “last updated” date, and (by design, I didn’t measure it) fires no row trigger and writes nothing to the redo log.

Only one branch#

You don’t need both branches. An update-only MERGE is a clean way to update a table from another one (here it updated the 3 existing products, SQL%ROWCOUNT = 3):

merge into products t
using product_feed s
   on (t.product_id = s.product_id)
 when matched then update set t.price = s.price;

An insert-only one adds only the missing rows (it inserted the 2 new ones), which is handy for a script that must be safe to run twice:

merge into products t
using product_feed s
   on (t.product_id = s.product_id)
 when not matched then insert (product_id, name, price)
                       values (s.product_id, s.name, s.price);

When I use MERGE#

• Syncing from a feed or staging table: a nightly import, a file a user uploads, rows from another system. One statement handles new, changed and unchanged rows.
• Saving a list the user edited in APEX: the page sends the edited rows as JSON, and one MERGE with a JSON_TABLE source writes them all (example below).
• Upserting one row from page items: a settings page where the row may or may not exist yet. The source is one row from dual.
• Deployment scripts that must be re-runnable: configuration rows, lookup values. WHEN NOT MATCHED only, or both branches, and the script runs twice without an “already exists” error.

What it does better than update-then-insert#

• One statement, one decision per row. The matched / not matched decision is made once, by the database, not by your code checking SQL%ROWCOUNT.
• Set-based. It works on all the rows in one go. The row-by-row loop at the top of this post is the slow way to do the same job (numbers below).
• The source can be anything you can SELECT: a table, a join, an aggregate, JSON_TABLE, a single row from dual.
• Conditions per branch. Skip unchanged rows, refuse some inserts, and delete some rows, all in one statement.

Speed: MERGE against the alternatives#

I filled a target table with 200,000 rows, then merged a source of 200,000 rows into it: 100,000 that already existed (with new values) and 100,000 new ones. Each approach was run twice and rolled back after each run:

So, honestly: on this test two set-based statements were slightly faster than MERGE, and both were about four times faster than the loop. The win of MERGE over approach B isn’t raw speed. It’s that the logic is in one place: B has to repeat the join in both statements and keep them in step, and its UPDATE updates every existing row unless you write the “has it changed” test twice too. The win over the loop is speed. If your code loops and upserts one row at a time, that’s the one to replace.

The DELETE clause, and its trap#

WHEN MATCHED can end with DELETE WHERE. Here it removes discontinued products:

merge into products t
using product_feed s
   on (t.product_id = s.product_id)
 when matched then
   update set t.price = s.price, t.updated_at = sysdate
   delete where s.action = 'D';

It worked: the Monitor (marked 'D') was deleted. Then I combined it with the “only if changed” WHERE from earlier:

 when matched then
   update set t.price = s.price, t.updated_at = sysdate
   where decode(t.price, s.price, 0, 1) = 1
   delete where s.action = 'D';

The Monitor was no longer deleted. Its price hadn’t changed, so the update’s WHERE skipped the row, and the DELETE only looks at rows the UPDATE actually updated. A row the update skips is never considered for deletion.

A second thing to know: DELETE WHERE sees the row after the update. With update set t.price = s.price delete where t.price = 0, a row whose new price is 0 was deleted, although its old price was 25.

If you need “delete these rows whatever happens”, give the UPDATE no WHERE, or delete them in a separate statement.

Upserting one row from APEX page items#

The source doesn’t have to be a table. One row from dual turns MERGE into a single-row upsert, for example in a page process:

merge into app_settings t
using (select :P10_USER_ID as user_id,
              :P10_THEME   as theme
         from dual) s
   on (t.user_id = s.user_id)
 when matched then update set t.theme = s.theme
 when not matched then insert (user_id, theme) values (s.user_id, s.theme);

No SELECT to check whether the row exists first, and no IF.

Saving an edited list sent as JSON#

The source can also be JSON_TABLE. When a page sends the edited rows as a JSON array (in a hidden item, or an Ajax callback parameter), one statement writes them all:

declare
  l_json clob := '[{"id":2,"price":12},{"id":4,"price":40,"name":"Webcam"}]';
begin
  merge into products t
  using (select j.id, j.name, j.price
           from json_table(l_json, '$[*]'
                  columns (id    number       path '$.id',
                           name  varchar2(50) path '$.name',
                           price number       path '$.price')) j) s
     on (t.product_id = s.id)
   when matched then update set t.price = s.price
   when not matched then insert (product_id, name, price) values (s.id, s.name, s.price);
end;
/

In my test it updated the Mouse to 12 and inserted the Webcam: SQL%ROWCOUNT = 2.

ORA-30926: the same target row twice#

If two source rows match the same target row, MERGE refuses:

ORA-30926: The operation attempted to update the same row (rowid: 'AAAYsCAAAAAADweAAA') twice.

It refused even when the two source rows carried identical values. The database won’t guess which one you meant, so the source must have one row per key.

The same duplicate on the insert side gives a different error. Two source rows with a new key both reached WHEN NOT MATCHED, and the second insert hit the primary key:

ORA-00001: unique constraint (...) violated on table PRODUCTS columns (PRODUCT_ID)

If the target had no unique key, both rows would have been inserted and you’d have a duplicate.

The fix for both is to remove the duplicates in the source, choosing which row wins. Here the latest one wins, assuming the feed has a loaded_at column that says when each row arrived:

merge into products t
using (select product_id, price
         from (select f.*,
                      row_number() over (partition by product_id order by loaded_at desc) as rn
                 from product_feed f)
        where rn = 1) s
   on (t.product_id = s.product_id)
 when matched then update set t.price = s.price;

ORA-38104: you can’t update a key you match on#

ORA-38104: Columns referenced in the ON Clause cannot be updated: "T"."PRODUCT_ID"

A column in ON can’t appear in UPDATE SET. If a key itself needs to change, that’s an UPDATE statement, not a MERGE.

A NULL key never matches#

ON uses =, and NULL = NULL isn’t true. I had a settings row with app_id = NULL (a global setting) and merged a new value for the same NULL app:

merge into settings t
using (select null as app_id, 'THEME' as setting, 'green' as val from dual) s
   on (t.app_id = s.app_id and t.setting = s.setting)
 when matched then update set t.val = s.val
 when not matched then insert values (s.app_id, s.setting, s.val);

The row wasn’t matched, so a second THEME row was inserted. Run it every day and you get a new duplicate every day. If a key column can be NULL, make the comparison treat two NULLs as equal:

   on (decode(t.app_id, s.app_id, 1, 0) = 1 and t.setting = s.setting)

With that ON, the existing row was updated to green and nothing was inserted. Better still, give the “global” rows a real value (0, say) instead of NULL, so a plain = and an index both work.

Triggers: more fire than you might expect#

I put a statement-level and a row-level trigger on the target table, each writing to a log, then merged one source row that matched an existing row:

STATEMENT INSERT
STATEMENT UPDATE
ROW UPDATE 1

The BEFORE INSERT statement trigger fired even though nothing was inserted. A MERGE fires the statement triggers of every operation it could do. With a DELETE clause, the DELETE statement trigger fires too. A row that is updated and then deleted fires its row trigger twice:

STATEMENT INSERT
STATEMENT UPDATE
STATEMENT DELETE
ROW UPDATE 1
ROW UPDATE 3
ROW DELETE 3
ROW INSERT 20

If a statement-level trigger on your table does real work “after every insert”, it will also run after a MERGE that only updated rows.

Two sessions inserting the same new key#

MERGE doesn’t lock a row that doesn’t exist yet. I ran two sessions at once:

• Session A merged product 50 (new, so it was inserted), then waited 8 seconds before committing.
• Session B, started during that wait, merged product 50 too.

Session B decided not matched (A’s row wasn’t committed), tried to insert, waited for A’s lock, and as soon as A committed it failed:

ORA-00001: unique constraint (...) violated on table PRODUCTS columns (PRODUCT_ID)

So MERGE is not a safe upsert under concurrency unless the target has a unique key. With the key, the worst case is that error. Without it, you’d get two rows. When two sessions really can upsert the same key at the same moment, catch the error and run the MERGE once more: the second time, the row exists and is updated.

procedure upsert_product(p_id number, p_name varchar2) is
begin
  for attempt in 1 .. 2 loop
    begin
      merge into products t
      using (select p_id as product_id, p_name as name from dual) s
         on (t.product_id = s.product_id)
       when matched then update set t.name = s.name
       when not matched then insert (product_id, name) values (s.product_id, s.name);
      return;
    exception
      when dup_val_on_index then
        if attempt = 2 then raise; end if;   -- give up after the second try
    end;
  end loop;
end;

I ran the same two-session test with this procedure. Session B’s first attempt hit ORA-00001, its second attempt updated A’s row, and the product ended with B’s name.

One bad row shouldn’t stop the whole feed: LOG ERRORS#

By default, one row that fails (a value too long, a check constraint) rolls back the whole MERGE. LOG ERRORS writes the bad rows to an error table and carries on:

exec dbms_errlog.create_error_log('PRODUCTS', 'PRODUCTS_ERR')

merge into products t
using product_feed s
   on (t.product_id = s.product_id)
 when matched then update set t.price = s.price
 when not matched then insert (product_id, name, price) values (s.product_id, s.name, s.price)
 log errors into products_err ('feed run 1') reject limit unlimited;

I merged three new rows, one with a 58-character name for a 50-character column. Two rows were inserted, and the third landed in PRODUCTS_ERR with the error number, the message and the tag:

ORA_ERR_NUMBER$  ORA_ERR_TAG$  PRODUCT_ID  ORA_ERR_MESG$
12899            feed run 1    6           ORA-12899: value too large for column ... (actual: 58, maximum: 50)

The tag ('feed run 1') tells you which run each error came from. After a run, read the error table: a MERGE that “succeeded” may have skipped rows.

What MERGE can’t do#

• It doesn’t tell you how many rows were updated and how many inserted. SQL%ROWCOUNT is the total. In the DELETE test it was 3: the three updated rows, one of which was then deleted.
• No RETURNING clause, even on 26ai. merge ... returning product_id bulk collect into ... failed with ORA-00925: missing INTO keyword. If you need the keys back, UPDATE and INSERT both support RETURNING.
• It can’t change the keys it matches on (ORA-38104, above).

Cheat sheet#

Rules I follow#

• One source row per key. Deduplicate with row_number() before the MERGE, or expect ORA-30926 (and ORA-00001 on the insert side).
• Match on a unique key, and keep it NOT NULL. A NULL key never matches; a missing unique key lets concurrent sessions insert duplicates.
• Add “has it changed” to the update, with decode so NULLs compare correctly.
• Don’t combine an update WHERE with DELETE WHERE unless you mean “delete only the rows that were also updated”.
• Remember the statement triggers: insert, update and delete triggers can all fire, whatever the rows did.
• Replace row-by-row upsert loops with MERGE. That’s where the speed is.
• Read the error table after a LOG ERRORS run.