Tutorial Oracle

Oracle MERGE INTO: Syntax and Examples (Upsert, Insert, Delete)

Oracle MERGE INTO syntax with 10+ examples: upsert, insert-only, conditional update, DELETE WHERE, a full example with sample data and results, and fixes for ORA-30926 and ORA-38104.

Published Updated 10 min read

Oracle MERGE INTO updates rows in a target table that match a source, and inserts the source rows that do not match, in one statement (an "upsert"). You define the match with an ON (...) condition, in parentheses. Oracle also lets the matched branch delete rows with DELETE WHERE.

Minimal upsert: update employee 101 if it exists, insert it if it does not.

MERGE INTO employees e
USING (SELECT 101 AS emp_id, 'Alice' AS name, 95000 AS salary FROM dual) s
ON (e.emp_id = s.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.name = s.name, e.salary = s.salary
WHEN NOT MATCHED THEN
    INSERT (emp_id, name, salary)
    VALUES (s.emp_id, s.name, s.salary);

What is MERGE INTO?

Oracle MERGE INTO is a single SQL statement that combines INSERT and UPDATE (and optionally DELETE) into one atomic operation. It is commonly called an UPSERT: if a matching row already exists in the target table, update it; if no match is found, insert a new row. The entire operation happens in one pass through the data, which is both cleaner to write and faster to execute than running separate conditional statements.

Oracle introduced MERGE in version 9i; MERGE was later added to the SQL standard in SQL:2003. Oracle 10g extended it with features that go beyond the standard: the DELETE clause, per-branch WHERE filters, and the option to use only one of the WHEN clauses. If you are working with Oracle 10g or later (including 11g, 12c, 18c, 19c, 21c, and 23ai), the examples in this tutorial apply without modification.

MERGE INTO is the correct tool whenever you need to synchronize two tables, load data from a staging area into a production table, or apply bulk changes where some rows already exist and others do not. It eliminates the pattern of querying whether a row exists, branching with PL/SQL IF statements, and issuing separate DML. One statement does it all.

Basic MERGE INTO Syntax

The structure of every Oracle MERGE statement follows the same skeleton:

MERGE INTO target_table  tgt          -- 1. The table you want to modify
USING source_table       src          -- 2. The data source (table, view, or subquery)
ON (tgt.id = src.id)                  -- 3. The join condition that defines a "match"
WHEN MATCHED THEN                     -- 4. What to do when a match is found
    UPDATE SET
        tgt.col1 = src.col1,
        tgt.col2 = src.col2
WHEN NOT MATCHED THEN                 -- 5. What to do when no match is found
    INSERT (col1, col2)
    VALUES (src.col1, src.col2);

Each numbered comment maps to a required part of the statement:

  • MERGE INTO target_table — the table that will be modified.
  • USING source — the data you are merging from. This can be a table, a view, or an inline subquery.
  • ON (condition) — the join predicate. Rows in target and source that satisfy this condition are considered "matched."
  • WHEN MATCHED THEN UPDATE — the action taken on target rows that have a corresponding source row.
  • WHEN NOT MATCHED THEN INSERT — the action taken when a source row has no matching target row.

Both WHEN clauses are optional, and when you use both they can appear in either order. You can write a MERGE with only WHEN MATCHED (bulk update with no inserts) or only WHEN NOT MATCHED (insert-only, no updates). You cannot write a MERGE with neither clause. The parentheses around the ON condition are required; leaving them out raises ORA-00969 (missing ON keyword).

Oracle MERGE Example with Sample Data

This example creates a target table and a source table, loads a few rows, runs one MERGE, and shows the result. Run it in SQL*Plus or SQL Developer in any schema where you can create tables.

1. Create the tables and sample rows

CREATE TABLE products (
    product_id NUMBER       PRIMARY KEY,
    name       VARCHAR2(50) NOT NULL,
    price      NUMBER(10,2) NOT NULL,
    status     VARCHAR2(10) NOT NULL
);

CREATE TABLE product_updates (
    product_id   NUMBER       PRIMARY KEY,
    name         VARCHAR2(50) NOT NULL,
    price        NUMBER(10,2) NOT NULL,
    discontinued CHAR(1)      NOT NULL
);

-- Target: what is in the table today
INSERT INTO products VALUES (1, 'Keyboard', 49.00,  'ACTIVE');
INSERT INTO products VALUES (2, 'Mouse',    19.00,  'ACTIVE');
INSERT INTO products VALUES (3, 'Monitor',  199.00, 'ACTIVE');

-- Source: today's feed
INSERT INTO product_updates VALUES (2, 'Mouse',   17.50,  'N');  -- price change
INSERT INTO product_updates VALUES (3, 'Monitor', 199.00, 'Y');  -- discontinued
INSERT INTO product_updates VALUES (4, 'Webcam',  59.00,  'N');  -- new product
COMMIT;

2. Run the MERGE

MERGE INTO products p
USING product_updates u
ON (p.product_id = u.product_id)
WHEN MATCHED THEN
    UPDATE SET p.name = u.name, p.price = u.price
    DELETE WHERE u.discontinued = 'Y'
WHEN NOT MATCHED THEN
    INSERT (product_id, name, price, status)
    VALUES (u.product_id, u.name, u.price, 'ACTIVE');

SELECT * FROM products ORDER BY product_id;

3. Result

product_id name price status What happened
1Keyboard49.00ACTIVENot in source, left unchanged
2Mouse17.50ACTIVEMatched, price updated
4Webcam59.00ACTIVENo match, inserted

Product 3 matched, was updated, and was then deleted because its source row has discontinued = 'Y'. Remove the DELETE WHERE line for a plain upsert: product 3 would stay in the table with price 199.00. Rows in the target that have no source row (product 1) are never touched by MERGE.

WHEN MATCHED THEN UPDATE

The WHEN MATCHED branch fires for every row in the source that has at least one matching row in the target according to the ON condition. The most common use is updating column values to reflect the latest data from the source.

-- Update product prices from a supplier feed
MERGE INTO products tgt
USING supplier_feed src
ON (tgt.product_id = src.product_id)
WHEN MATCHED THEN
    UPDATE SET
        tgt.unit_price   = src.unit_price,
        tgt.last_updated = SYSDATE
WHERE tgt.unit_price != src.unit_price;  -- Only update if price actually changed

The WHERE clause inside WHEN MATCHED is an Oracle extension that adds a row-level filter on top of the ON condition. In this example, rows where the price has not changed are skipped entirely, saving unnecessary redo log writes. This is particularly valuable in large batch loads where the majority of rows may be identical to what is already stored. Note that != is never true when either side is NULL, so if the column is nullable, add the NULL cases to the condition.

One important constraint applies to the UPDATE in MERGE: you cannot update a column referenced in the ON condition (Oracle raises ORA-38104: columns referenced in the ON clause cannot be updated). This prevents the update from changing the key that decided whether the row matched in the first place. See common errors below for a workaround.

WHEN NOT MATCHED THEN INSERT (Insert-Only MERGE)

The WHEN NOT MATCHED branch fires for every source row that has no corresponding row in the target table. It works like a regular INSERT, specifying the column list and the values to populate from the source.

A MERGE with only this branch is an insert-only MERGE: it adds the missing rows and leaves existing rows alone. With the sample tables above (before running the upsert), this inserts only product 4:

-- Oracle MERGE insert only: add new products, never update existing ones
MERGE INTO products p
USING product_updates u
ON (p.product_id = u.product_id)
WHEN NOT MATCHED THEN
    INSERT (product_id, name, price, status)
    VALUES (u.product_id, u.name, u.price, 'ACTIVE');

Products 2 and 3 already exist, so they are skipped, and their prices stay 19.00 and 199.00. The same result is possible with INSERT ... SELECT ... WHERE NOT EXISTS; MERGE is handy when you may add a WHEN MATCHED branch later.

A more typical insert-only case generates the key from a sequence and filters bad rows:

-- Insert new customers that do not yet exist in the main table
MERGE INTO customers tgt
USING new_signups src
ON (tgt.email = src.email)
WHEN NOT MATCHED THEN
    INSERT (customer_id, email, full_name, signup_date, status)
    VALUES (
        customer_seq.NEXTVAL,
        src.email,
        src.full_name,
        SYSDATE,
        'ACTIVE'
    )
WHERE src.email IS NOT NULL;  -- Guard against null email rows in source

Notice that the source has no customer_id. The sequence customer_seq.NEXTVAL is called inside the VALUES clause to generate a new key. This is the standard pattern for surrogate key generation in Oracle MERGE.

The optional WHERE clause on WHEN NOT MATCHED filters which unmatched source rows are actually inserted. This is useful to skip bad data (null required fields, out-of-range values) without needing a pre-cleaning step.

WHEN MATCHED THEN DELETE

Oracle extends the SQL standard with a DELETE option inside the WHEN MATCHED branch. When a row satisfies the match condition and also satisfies an additional DELETE WHERE predicate, the row is deleted from the target instead of updated.

-- Sync inventory: update quantities, remove discontinued items
MERGE INTO inventory tgt
USING inventory_update src
ON (tgt.sku = src.sku)
WHEN MATCHED THEN
    UPDATE SET
        tgt.quantity    = src.quantity,
        tgt.last_sync   = SYSDATE
    DELETE WHERE src.discontinued = 'Y';  -- Remove matched rows marked discontinued

The DELETE WHERE clause is evaluated after the UPDATE SET has been applied, so it sees the updated values of the target row, and it can also reference source columns as shown here. Only rows that the MERGE matched and updated can be deleted: a target row with no source match is never deleted, even if it meets the condition. If the UPDATE has its own WHERE filter, rows skipped by that filter are not deleted either. DELETE cannot be used without the UPDATE clause; you cannot write WHEN MATCHED THEN DELETE alone. If you need to delete without updating, use a separate DELETE statement.

Using MERGE with Subqueries as Source

The USING clause accepts any valid query expression, not just a table or view name. Using a subquery as the source is one of the most powerful patterns in Oracle MERGE because it lets you aggregate, filter, and transform data before merging.

-- Merge daily order totals into a summary table
MERGE INTO order_summary tgt
USING (
    SELECT
        customer_id,
        TRUNC(order_date) AS order_day,
        SUM(amount)       AS daily_total,
        COUNT(*)          AS order_count
    FROM orders
    WHERE order_date >= TRUNC(SYSDATE) - 7
    GROUP BY customer_id, TRUNC(order_date)
) src
ON (
    tgt.customer_id = src.customer_id
    AND tgt.summary_date = src.order_day
)
WHEN MATCHED THEN
    UPDATE SET
        tgt.total_amount = src.daily_total,
        tgt.order_count  = src.order_count
WHEN NOT MATCHED THEN
    INSERT (customer_id, summary_date, total_amount, order_count)
    VALUES (src.customer_id, src.order_day, src.daily_total, src.order_count);

The inline view aggregates the last 7 days of orders per customer per day before the merge even starts. This is far more efficient than merging the raw orders table (potentially millions of rows) against order_summary, because the aggregation reduces the source to only the rows that matter. Filter the source as tightly as you can inside the subquery, since every source row is joined against the target.

Conditional MERGE with WHERE Clauses

Both the WHEN MATCHED and WHEN NOT MATCHED branches accept an independent WHERE clause that applies additional row-level filtering beyond the ON condition. This allows a single MERGE statement to handle complex business rules that would otherwise require multiple passes or procedural code.

-- Selective sync: only update active accounts, only insert verified leads
MERGE INTO accounts tgt
USING crm_export src
ON (tgt.account_number = src.account_number)
WHEN MATCHED THEN
    UPDATE SET
        tgt.balance      = src.balance,
        tgt.credit_limit = src.credit_limit,
        tgt.modified_at  = SYSDATE
    WHERE tgt.status = 'ACTIVE'          -- Skip frozen or closed accounts
WHEN NOT MATCHED THEN
    INSERT (account_number, owner_name, balance, status, created_at)
    VALUES (src.account_number, src.owner_name, src.balance, 'ACTIVE', SYSDATE)
    WHERE src.verified = 'Y';            -- Only insert verified new accounts

Each WHERE clause is independent. A matched row that fails the WHEN MATCHED WHERE condition is simply skipped — it is neither updated nor deleted. An unmatched row that fails the WHEN NOT MATCHED WHERE condition is simply not inserted. The statement completes without error in either case. This makes conditional MERGE ideal for idempotent data pipelines where re-running the statement produces the same result.

Real Example: Syncing a Staging Table to Production

The most common production use of Oracle MERGE is loading from a staging table populated by an ETL job into the live operational table. The staging table contains the full set of changed rows — updates and new inserts mixed together — and MERGE handles the routing automatically.

-- Full staging-to-production sync for employee records
MERGE INTO hr.employees tgt
USING (
    SELECT
        employee_id,
        first_name,
        last_name,
        department_id,
        salary,
        job_title,
        hire_date,
        termination_date
    FROM staging.employees_delta
    WHERE load_batch_id = (SELECT MAX(load_batch_id) FROM staging.employees_delta)
) src
ON (tgt.employee_id = src.employee_id)
WHEN MATCHED THEN
    UPDATE SET
        tgt.first_name      = src.first_name,
        tgt.last_name       = src.last_name,
        tgt.department_id   = src.department_id,
        tgt.salary          = src.salary,
        tgt.job_title       = src.job_title
    DELETE WHERE src.termination_date IS NOT NULL  -- Remove terminated employees
WHEN NOT MATCHED THEN
    INSERT (
        employee_id, first_name, last_name,
        department_id, salary, job_title, hire_date
    )
    VALUES (
        src.employee_id, src.first_name, src.last_name,
        src.department_id, src.salary, src.job_title, src.hire_date
    )
    WHERE src.termination_date IS NULL;  -- Do not insert already-terminated employees

This single statement handles three scenarios: updating existing active employees, deleting employees who have been terminated (matched rows with a termination date), and inserting new hires who do not yet exist in the production table. The subquery in the USING clause isolates only the latest batch, making the statement safe to run repeatedly without reprocessing old data.

Syncing your own tables? Generate the MERGE statement for your staging and target tables with AI2SQL.

MERGE vs INSERT ... ON DUPLICATE KEY (Oracle vs MySQL)

Developers who work across both Oracle and MySQL often need to translate between two different UPSERT syntaxes. The table below summarizes the key differences:

Feature Oracle MERGE INTO MySQL ON DUPLICATE KEY UPDATE
Match definition Explicit ON condition (any expression) Implicit: unique/primary key conflict
Source data Table, view, or subquery VALUES list or INSERT ... SELECT
Conditional logic per branch Yes, WHERE clause on each WHEN No WHERE per branch (use IF() in the SET expressions)
DELETE on match Yes (Oracle extension) No
Multiple source rows per target Not allowed (ORA-30926) Processes each row independently
SQL standard compliance SQL:2003 MERGE (extended) MySQL-specific, non-standard

The equivalent of a simple MySQL UPSERT in Oracle looks like this side by side:

-- MySQL
INSERT INTO employees (id, name, salary)
VALUES (101, 'Alice', 95000)
ON DUPLICATE KEY UPDATE
    name   = VALUES(name),
    salary = VALUES(salary);

-- Oracle equivalent
MERGE INTO employees tgt
USING (
    SELECT 101 AS id, 'Alice' AS name, 95000 AS salary FROM DUAL
) src
ON (tgt.id = src.id)
WHEN MATCHED THEN
    UPDATE SET tgt.name = src.name, tgt.salary = src.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary)
    VALUES (src.id, src.name, src.salary);

Oracle's syntax is more verbose for single-row operations but scales more cleanly to bulk data loads. The explicit ON condition also makes the matching logic transparent and auditable, which matters in compliance-sensitive environments.

Performance Tips

MERGE INTO is efficient by design, but large-scale merges require additional tuning to perform well in production.

1. Index the ON condition columns

The columns referenced in the ON clause drive the join between source and target. Ensure the target table has an index on those columns. For a MERGE matching on customer_id, a B-tree index on target.customer_id is essential. Without it, Oracle performs a full table scan of the target for every source row, which degrades to O(n*m) complexity.

2. Use parallel hints for bulk loads

For very large merges (millions of rows), enable parallel DML:

ALTER SESSION ENABLE PARALLEL DML;

MERGE /*+ PARALLEL(tgt, 4) */ INTO sales_summary tgt
USING (SELECT /*+ PARALLEL(src, 4) */ * FROM sales_staging src) src
ON (tgt.sale_id = src.sale_id)
WHEN MATCHED THEN
    UPDATE SET tgt.amount = src.amount
WHEN NOT MATCHED THEN
    INSERT (sale_id, amount, sale_date)
    VALUES (src.sale_id, src.amount, src.sale_date);

The PARALLEL hint instructs Oracle to use four parallel query servers for both the source scan and the target DML. Always test parallel degree on a representative data volume — for smaller tables, parallel setup overhead can make performance worse, not better.

3. Prevent ORA-30926 with deterministic source data

Oracle raises ORA-30926 ("unable to get a stable set of rows in the source tables") when the USING subquery returns more than one row that matches the same target row via the ON condition. This is a logic error, not a tuning issue, but it surfaces most often in large merges where the source has accidental duplicates. Deduplicate the source before merging:

-- Deduplicate source using ROW_NUMBER before merging
MERGE INTO target_table tgt
USING (
    SELECT *
    FROM (
        SELECT
            id, col1, col2,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
        FROM staging_table
    )
    WHERE rn = 1  -- Keep only the latest row per id
) src
ON (tgt.id = src.id)
WHEN MATCHED THEN
    UPDATE SET tgt.col1 = src.col1, tgt.col2 = src.col2
WHEN NOT MATCHED THEN
    INSERT (id, col1, col2)
    VALUES (src.id, src.col1, src.col2);

4. Batch large merges

For extremely large staging tables, a single MERGE can hold locks for an extended period and generate excessive undo. Batching limits the transaction size and keeps the system responsive. This version flags a slice of staging rows, merges only that slice, then marks it done (it assumes staging.id is unique, otherwise see ORA-30926 above):

-- Process 100,000 staging rows per batch
BEGIN
    LOOP
        -- 1. Claim the next batch
        UPDATE staging
           SET processed = 'P'
         WHERE processed = 'N'
           AND ROWNUM <= 100000;

        EXIT WHEN SQL%ROWCOUNT = 0;

        -- 2. Merge only the claimed rows
        MERGE INTO target tgt
        USING (SELECT id, val FROM staging WHERE processed = 'P') src
        ON (tgt.id = src.id)
        WHEN MATCHED THEN
            UPDATE SET tgt.val = src.val
        WHEN NOT MATCHED THEN
            INSERT (id, val) VALUES (src.id, src.val);

        -- 3. Mark the batch done and commit
        UPDATE staging SET processed = 'Y' WHERE processed = 'P';
        COMMIT;
    END LOOP;
END;
/

5. Gather statistics after bulk merges

After a large MERGE that significantly changes table row counts, the optimizer's statistics are stale. Run DBMS_STATS.GATHER_TABLE_STATS on both the target table and its indexes to ensure subsequent queries use accurate execution plans.

Common Oracle MERGE Errors

Error Cause Fix
ORA-30926: unable to get a stable set of rows in the source tables More than one source row matches the same target row. Deduplicate the source (ROW_NUMBER example above) or tighten the ON condition.
ORA-38104: columns referenced in the ON clause cannot be updated The UPDATE SET list includes a column used in ON. Remove it from SET, or match on ROWID (example below).
ORA-00969: missing ON keyword The ON condition is not wrapped in parentheses. Write ON (tgt.id = src.id).

If you really need to change a column you match on, join source and target inside the USING subquery and match on the target ROWID instead:

-- Update the email column even though rows are found by email
MERGE INTO customers tgt
USING (
    SELECT c.ROWID AS rid, u.new_email
    FROM customers c
    JOIN email_changes u ON u.old_email = c.email
) src
ON (tgt.ROWID = src.rid)
WHEN MATCHED THEN
    UPDATE SET tgt.email = src.new_email;

How AI2SQL Generates Oracle MERGE Queries

Writing a production-ready MERGE statement from scratch is error-prone. Getting the ON condition right, choosing whether to use WHERE filters on each branch, handling the ORA-30926 deduplication pattern, and adding the correct parallel hints all require Oracle-specific knowledge that takes time to internalize.

AI2SQL generates Oracle MERGE statements from plain English descriptions. Set the dialect to Oracle and describe what you want:

  • "Merge the staging_orders table into orders. Update amount and status for existing order IDs. Insert new orders that don't exist yet."
  • "Sync the employee_delta staging table to hr.employees. Update active employees, delete terminated ones, insert new hires."
  • "Upsert daily revenue totals from the raw_transactions table into the revenue_summary table, grouped by customer and date."

AI2SQL produces a complete MERGE with the correct clause structure, column mappings, and WHERE guards — including deduplication when the source might have duplicates. You get production-ready Oracle SQL without having to remember whether DELETE goes before or after the WHERE clause, or whether the UPDATE columns can overlap with the ON condition columns.

For teams working across multiple databases, AI2SQL also handles dialect translation. Describe a MySQL ON DUPLICATE KEY UPDATE scenario and ask for the Oracle equivalent — the output is a correctly structured MERGE INTO with the explicit ON condition and proper USING clause that Oracle requires.

The most effective workflow is to use AI2SQL as a starting point, review the generated MERGE against your actual table structures, and add any environment-specific hints or batching logic your production setup requires. This cuts the time from requirement to working SQL from hours to minutes, especially for complex staging pipelines that combine updates, inserts, and conditional deletes in a single operation.

Frequently Asked Questions

What is Oracle MERGE INTO used for?

Oracle MERGE INTO is used to perform an UPSERT operation: it inserts a new row if no match is found in the target table, or updates an existing row if a match is found. This is more efficient than running separate INSERT and UPDATE statements because it processes both in a single pass through the data.

Can MERGE INTO delete rows in Oracle?

Yes. Oracle adds an optional DELETE WHERE clause to the WHEN MATCHED THEN UPDATE branch. It only affects target rows that the MERGE matched and updated, and the condition is checked against the updated values. Matched rows that meet the condition are deleted; the rest stay updated. DELETE cannot be used on its own without UPDATE SET.

Does Oracle MERGE INTO support conditional logic?

Yes. Both WHEN MATCHED and WHEN NOT MATCHED clauses accept an optional WHERE predicate that adds row-level conditions beyond the ON join condition. This lets you update only rows where a value has changed, or insert only rows that meet a specific filter, giving fine-grained control over which rows are affected.

How is Oracle MERGE INTO different from MySQL INSERT ... ON DUPLICATE KEY UPDATE?

Oracle MERGE INTO names a source (table, view, or subquery) in the USING clause and defines a match with an explicit ON condition. MySQL INSERT ... ON DUPLICATE KEY UPDATE matches on a primary key or unique index conflict and takes its rows from VALUES or a SELECT. Oracle's syntax is more verbose but adds a WHERE filter on each branch and an optional DELETE clause, which MySQL's statement does not have.

Can Oracle MERGE insert only, without updating?

Yes. Write the MERGE with only a WHEN NOT MATCHED THEN INSERT clause. Source rows with no match in the target are inserted, and rows that already exist are left unchanged. Since Oracle 10g, either WHEN clause can be used on its own.

Generate Oracle MERGE Queries Instantly

Describe your UPSERT logic in plain English and AI2SQL writes the complete Oracle MERGE INTO statement — with correct clause structure, deduplication guards, and performance hints.

Get Started Free

No credit card required