Oracle MERGE Statement Example: Upsert, Update and Delete

The Oracle MERGE statement lets you insert, update and even delete rows in one table based on the rows of another table, in a single SQL statement. You give it a target table, a source, and a join condition. Rows that match get updated, rows that don't match get inserted. This is what most people call an upsert.

This tutorial walks through a complete Oracle MERGE statement example on a small product table, then adds conditional updates, conditional inserts, the DELETE clause, a single-row upsert from PL/SQL, and the two errors almost everyone hits: ORA-30926 and ORA-38104. Every example uses the same sample data, so you can run them one after another.

How the MERGE statement works

MERGE joins a source (a table, view or subquery) to a target table using the condition in the ON clause. For each source row, Oracle checks whether the condition finds a target row.

• If it does, the WHEN MATCHED THEN UPDATE branch runs for that target row.
• If it does not, the WHEN NOT MATCHED THEN INSERT branch inserts a new row built from the source row.
• Optionally, a DELETE WHERE clause inside the matched branch removes rows that were just updated.

Here is the basic shape:

MERGE INTO target_table t
USING source_table s
ON (t.key_column = s.key_column)
WHEN MATCHED THEN
  UPDATE SET t.col1 = s.col1,
             t.col2 = s.col2
WHEN NOT MATCHED THEN
  INSERT (key_column, col1, col2)
  VALUES (s.key_column, s.col1, s.col2);

Three things in this skeleton matter more than they look. The ON condition must be wrapped in parentheses in Oracle. You need at least one of the two branches, but not both. And the source drives everything: target rows that have no matching source row are never touched.

Why use MERGE instead of an UPDATE followed by an INSERT? Two separate statements read the data twice and need extra logic (usually NOT EXISTS) to avoid inserting rows that already exist. MERGE does the join once, in one statement, and the whole thing succeeds or fails as a unit.

Sample setup

You will merge a daily supplier feed into a products table. The products table is the target. The product_feed table is the source, the kind of staging table you load from a CSV file.

CREATE TABLE products (
  product_id   NUMBER        PRIMARY KEY,
  product_name VARCHAR2(50)  NOT NULL,
  price        NUMBER(10,2)  NOT NULL,
  status       VARCHAR2(15)  DEFAULT 'ACTIVE' NOT NULL
);

CREATE TABLE product_feed (
  product_id   NUMBER,
  product_name VARCHAR2(50),
  price        NUMBER(10,2),
  discontinued CHAR(1)
);

INSERT INTO products VALUES (1, 'USB-C Cable',    299,  'ACTIVE');
INSERT INTO products VALUES (2, 'Wireless Mouse', 799,  'ACTIVE');
INSERT INTO products VALUES (3, 'Laptop Stand',   1499, 'ACTIVE');

INSERT INTO product_feed VALUES (2, 'Wireless Mouse', 749,  'N');
INSERT INTO product_feed VALUES (3, 'Laptop Stand',   1499, 'Y');
INSERT INTO product_feed VALUES (4, 'Webcam HD',      2499, 'N');
INSERT INTO product_feed VALUES (5, 'Old Keyboard',   999,  'Y');

COMMIT;

Look at the feed before going on. Product 2 has a new price. Product 3 has the same price but is now discontinued. Products 4 and 5 are new, and product 5 is already discontinued. Product 1 is not in the feed at all.

Example 1: a basic upsert

The simplest MERGE updates matched products with the feed values and inserts the new ones.

MERGE INTO products p
USING product_feed f
ON (p.product_id = f.product_id)
WHEN MATCHED THEN
  UPDATE SET p.product_name = f.product_name,
             p.price        = f.price
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, price)
  VALUES (f.product_id, f.product_name, f.price);

SELECT * FROM products ORDER BY product_id;

PRODUCT_ID PRODUCT_NAME     PRICE STATUS
---------- -------------- ------- -------
         1 USB-C Cable        299 ACTIVE
         2 Wireless Mouse     749 ACTIVE
         3 Laptop Stand      1499 ACTIVE
         4 Webcam HD         2499 ACTIVE
         5 Old Keyboard       999 ACTIVE

Products 2 and 3 matched, so they were updated. Product 2 now costs 749. Product 3 was "updated" to the same values it already had. Products 4 and 5 did not match, so they were inserted, and their status came from the column default because the INSERT column list left it out. Product 1 was not in the feed, so it is unchanged.

Two problems are visible in this result. Product 3 was rewritten for no reason, and product 5 was added even though the supplier has discontinued it. The next example fixes both.

ROLLBACK;

Example 2: conditional update and insert with WHERE

Both branches accept an optional WHERE clause. A row that fails the WHERE condition is simply skipped by that branch.

MERGE INTO products p
USING product_feed f
ON (p.product_id = f.product_id)
WHEN MATCHED THEN
  UPDATE SET p.product_name = f.product_name,
             p.price        = f.price
  WHERE p.price <> f.price
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, price)
  VALUES (f.product_id, f.product_name, f.price)
  WHERE f.discontinued = 'N';

SELECT * FROM products ORDER BY product_id;

PRODUCT_ID PRODUCT_NAME     PRICE STATUS
---------- -------------- ------- -------
         1 USB-C Cable        299 ACTIVE
         2 Wireless Mouse     749 ACTIVE
         3 Laptop Stand      1499 ACTIVE
         4 Webcam HD         2499 ACTIVE

Now only product 2 was updated, because it is the only matched row whose price changed. Product 4 was inserted. Product 5 was skipped by the insert branch because its discontinued flag is Y.

There is one rule to remember here. The update WHERE clause can refer to both the source and the target. The insert WHERE clause can refer only to source columns, which makes sense: when no row matched, there is no target row to look at.

ROLLBACK;

Example 3: delete rows with MERGE DELETE WHERE

The matched branch can also delete rows. The DELETE WHERE clause goes after the UPDATE SET (and its optional WHERE), and it is the part of MERGE that confuses people most, so take it one step at a time.

Step 1: write the statement

Here the feed marks product 3 as discontinued. You want to remove discontinued products from the table during the same merge.

MERGE INTO products p
USING product_feed f
ON (p.product_id = f.product_id)
WHEN MATCHED THEN
  UPDATE SET p.price  = f.price,
             p.status = CASE f.discontinued
                          WHEN 'Y' THEN 'DISCONTINUED'
                          ELSE 'ACTIVE'
                        END
  DELETE WHERE p.status = 'DISCONTINUED'
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, price)
  VALUES (f.product_id, f.product_name, f.price)
  WHERE f.discontinued = 'N';

SELECT * FROM products ORDER BY product_id;

PRODUCT_ID PRODUCT_NAME     PRICE STATUS
---------- -------------- ------- -------
         1 USB-C Cable        299 ACTIVE
         2 Wireless Mouse     749 ACTIVE
         4 Webcam HD         2499 ACTIVE

Step 2: understand what happened

Product 3 matched, so the UPDATE set its status to DISCONTINUED. Then the DELETE WHERE checked the row and found status = 'DISCONTINUED', so it deleted it. The key point is that DELETE WHERE sees the updated values, not the values the row had before the merge. The original status of product 3 was ACTIVE, yet it was deleted.

Product 2 was updated and its new status is ACTIVE, so it stays. Product 4 was inserted. Product 5 was skipped by the insert WHERE.

Step 3: know what DELETE WHERE cannot do

DELETE WHERE only acts on rows that the merge has just updated. Two consequences follow:

• A target row that is not in the source is never deleted. Product 1 is missing from the feed, but MERGE has no way to remove it. If you need "delete what is not in the feed", run a separate DELETE ... WHERE NOT EXISTS.
• A matched row that the update WHERE clause skipped is not updated, so it is not a candidate for deletion either.

ROLLBACK;

Example 4: update-only and insert-only MERGE

You do not have to write both branches. An update-only MERGE is a clean way to update one table from another, which is awkward with a plain UPDATE because Oracle's UPDATE has no FROM clause.

MERGE INTO products p
USING product_feed f
ON (p.product_id = f.product_id)
WHEN MATCHED THEN
  UPDATE SET p.price = f.price;

This touches products 2 and 3 only. New products in the feed are ignored because there is no NOT MATCHED branch.

An insert-only MERGE adds missing rows and leaves existing ones alone. It replaces the common INSERT ... SELECT ... WHERE NOT EXISTS pattern:

MERGE INTO products p
USING product_feed f
ON (p.product_id = f.product_id)
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, price)
  VALUES (f.product_id, f.product_name, f.price);

SELECT product_id, product_name, price FROM products ORDER BY product_id;

ROLLBACK;

PRODUCT_ID PRODUCT_NAME     PRICE
---------- -------------- -------
         1 USB-C Cable        299
         2 Wireless Mouse     799
         3 Laptop Stand      1499
         4 Webcam HD         2499
         5 Old Keyboard       999

Products 2 and 3 keep their old prices, and products 4 and 5 are added.

Example 5: upsert a single row from PL/SQL

Often there is no source table at all. An application or procedure has a few values and wants to "insert or update" one row. Build a one-row source from DUAL with the values as parameters or bind variables:

CREATE OR REPLACE PROCEDURE save_product (
  p_id    IN products.product_id%TYPE,
  p_name  IN products.product_name%TYPE,
  p_price IN products.price%TYPE
) AS
BEGIN
  MERGE INTO products p
  USING (SELECT p_id    AS product_id,
                p_name  AS product_name,
                p_price AS price
           FROM dual) s
  ON (p.product_id = s.product_id)
  WHEN MATCHED THEN
    UPDATE SET p.product_name = s.product_name,
               p.price        = s.price
  WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price)
    VALUES (s.product_id, s.product_name, s.price);
END save_product;
/

BEGIN
  save_product(1, 'USB-C Cable 2m', 349);   -- exists: updated
  save_product(6, 'HDMI Cable',     399);   -- new: inserted
END;
/

SELECT * FROM products ORDER BY product_id;

PRODUCT_ID PRODUCT_NAME     PRICE STATUS
---------- -------------- ------- -------
         1 USB-C Cable 2m     349 ACTIVE
         2 Wireless Mouse     799 ACTIVE
         3 Laptop Stand      1499 ACTIVE
         6 HDMI Cable         399 ACTIVE

The caller does not need to check first whether the row exists. That check-then-act pattern (SELECT COUNT(*), then INSERT or UPDATE) is slower, and between the check and the write another session may already have inserted the same key.

ROLLBACK;

Common mistakes and how to fix them

ORA-30926: duplicate keys in the source

MERGE is deterministic: Oracle will not update the same target row twice in one statement. If the source contains two rows with the same key, both match one target row and the statement fails.

INSERT INTO product_feed VALUES (2, 'Wireless Mouse', 699, 'N');

MERGE INTO products p
USING product_feed f
ON (p.product_id = f.product_id)
WHEN MATCHED THEN
  UPDATE SET p.price = f.price;

ORA-30926: unable to get a stable set of rows in the source tables

Oracle 19c reports the message above. The current Oracle error documentation also lists a newer wording for the same code: "The operation attempted to update the same row (rowid: '...') twice." Either way, the fix is to make the source return one row per key. Decide which duplicate wins and filter the others out:

MERGE INTO products p
USING (SELECT product_id, product_name, price, discontinued
         FROM (SELECT f.*,
                      ROW_NUMBER() OVER (PARTITION BY product_id
                                         ORDER BY price) AS rn
                 FROM product_feed f)
        WHERE rn = 1) s
ON (p.product_id = s.product_id)
WHEN MATCHED THEN
  UPDATE SET p.price = s.price;

ROLLBACK;

In real feeds you would order by a load timestamp so the latest row wins. Find duplicates before merging with GROUP BY product_id HAVING COUNT(*) > 1.

ORA-38104: updating a column used in the ON clause

You cannot change a column that the ON condition uses, because changing it would change which rows match.

MERGE INTO products p
USING product_feed f
ON (p.product_id = f.product_id)
WHEN MATCHED THEN
  UPDATE SET p.product_id = f.product_id,
             p.price      = f.price;

ORA-38104: Columns referenced in the ON Clause cannot be updated: <column name>

Remove the join column from UPDATE SET. Its value is already equal on both sides, so there is nothing to update.

Other mistakes

• Missing parentheses around ON. Oracle requires ON (condition). Writing ON p.id = f.id without them is a syntax error.
• NULL keys in the source. A NULL never equals anything, so a source row with a NULL key always goes to the insert branch. With a NOT NULL primary key it fails, without one it creates junk rows. Filter NULL keys out of the source.
• Expecting MERGE to delete rows missing from the source. It cannot. Use a separate DELETE with NOT EXISTS.
• Referencing the target in the insert WHERE clause. Only source columns are allowed there.
• Forgetting to COMMIT. MERGE is ordinary DML. Nothing is permanent until the transaction commits.

Performance tips

• Use one set-based MERGE, not a MERGE per row in a loop. A single statement over the whole staging table lets the optimizer choose a hash join and avoids a round trip per row. Keep the single-row pattern from Example 5 for application calls that really handle one row.
• Index the target join column. When the source is small and the target is large, an index on the target key (usually the primary key) lets Oracle find matches without scanning the whole table.
• Keep the source narrow. Select only the columns and rows you need in the USING subquery. Filtering there is cheaper than filtering in the branch WHERE clauses.
• Skip unchanged rows with an update WHERE clause, as in Example 2, to cut redo, undo and trigger work.
• Check the plan with EXPLAIN PLAN like any other statement. A MERGE plan shows the join between source and target, and that join is where the time goes.

For large loads where a few bad rows should not roll back the whole statement, MERGE supports DML error logging, which behaves the same way as for INSERT. Create the error table once, then add the LOG ERRORS clause:

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('PRODUCTS');

MERGE INTO products p
USING product_feed f
ON (p.product_id = f.product_id)
WHEN MATCHED THEN
  UPDATE SET p.price = f.price
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, price)
  VALUES (f.product_id, f.product_name, f.price)
LOG ERRORS INTO err$_products ('feed load') REJECT LIMIT UNLIMITED;

Rows that fail, for example a NULL price hitting the NOT NULL constraint, are written to ERR$_PRODUCTS with the error message and the tag 'feed load', and the rest of the merge continues.

Frequently asked questions

Is MERGE faster than separate UPDATE and INSERT statements?

Usually, yes. MERGE joins source and target once, while separate statements read the data at least twice and need a NOT EXISTS check for the insert. The bigger gain is avoiding row-by-row processing. Check the execution plan for your own data rather than assuming.

Can I use a WITH clause (CTE) in an Oracle MERGE?

Not before the MERGE keyword. Put the WITH clause inside the USING subquery instead: USING (WITH latest AS (...) SELECT ... FROM latest) s.

Does MERGE fire triggers in Oracle?

Yes. If the update branch runs, the update triggers on the target fire. If the insert branch runs, the insert triggers fire. Rows removed by DELETE WHERE fire delete triggers.

Can MERGE update the same row twice?

No. If two source rows match one target row, the statement fails with ORA-30926. De-duplicate the source first, as shown above.

What privileges do I need to run MERGE?

INSERT and UPDATE on the target table, SELECT on the source, and DELETE on the target if you use the DELETE WHERE clause.

Summary

• MERGE INTO target USING source ON (condition) updates matched rows and inserts unmatched ones in one statement.
• Wrap the ON condition in parentheses and make sure the source returns one row per key, or you get ORA-30926.
• Never update a column used in the ON clause (ORA-38104).
• Use the update WHERE clause to skip unchanged rows. The insert WHERE clause can use only source columns.
• DELETE WHERE sees updated values and only removes rows the merge just updated, never rows missing from the source.
• Use a one-row USING (SELECT ... FROM dual) source for single-row upserts, and keep a unique constraint on the key.
• Merge whole sets, not one row at a time in a loop, and consider LOG ERRORS for large loads.