Oracle MERGE INTO: Complete Tutorial with Examples

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, conforming to the SQL:1999 standard. The syntax has since been extended with Oracle-specific features — notably the DELETE clause and per-branch WHERE filters — that go beyond what the standard requires. If you are working with Oracle 9i or later (including 10g, 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. You can write a MERGE with only WHEN MATCHED (bulk update with no inserts) or only WHEN NOT MATCHED (conditional insert with no updates). You cannot write a MERGE with neither clause.

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.

A few important constraints apply to the UPDATE in MERGE: you cannot update the columns used in the ON condition (Oracle will raise ORA-38104). This prevents a situation where the update would change the key that determined whether the row matched in the first place, which would make the operation semantically ambiguous.

WHEN NOT MATCHED THEN INSERT

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.

-- 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 INSERT column list does not include the target primary key customer_id as it exists in the source — instead 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. A row is deleted if, after the update, it satisfies the DELETE WHERE condition. This means the condition can reference the just-updated values of the target row. It cannot be used without the UPDATE clause — you cannot have WHEN MATCHED THEN DELETE alone. If you need to delete without first 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. Oracle's optimizer can often push predicates from the ON clause into the subquery as well, further limiting the data scanned.

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.

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 only (no external source)
Conditional logic per branch	Yes, WHERE clause on each WHEN	No — all matched rows are updated
DELETE on match	Yes (Oracle extension)	No
Multiple source rows per target	Not allowed (ORA-30926)	Processes each row independently
SQL standard compliance	SQL:1999 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 with ROWNUM slicing

For extremely large staging tables, a single MERGE can hold locks for an extended period and generate excessive undo. Batching by primary key range or with ROWNUM limits the transaction size and keeps the system responsive:

-- Process 100,000 rows per batch
BEGIN
    LOOP
        MERGE INTO target tgt
        USING (
            SELECT * FROM staging WHERE processed = 'N'
            AND ROWNUM <= 100000
        ) 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);

        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;

        -- Mark processed rows
        UPDATE staging SET processed = 'Y'
        WHERE id IN (SELECT id FROM target WHERE ...);
        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.

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 extends the standard MERGE syntax with a DELETE clause inside WHEN MATCHED. You add a WHERE condition after the UPDATE clause to specify which matched rows should be deleted instead of updated. Only rows that satisfy the DELETE WHERE condition are removed; others are updated normally.

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 uses a full FROM source clause and an explicit ON join condition, making it clear which key defines a match. MySQL INSERT ... ON DUPLICATE KEY UPDATE relies on a unique index conflict and has no source table concept. Oracle's syntax is more verbose but also more flexible: you can merge from subqueries, apply WHERE filters on each branch, and include a DELETE clause, none of which are possible with MySQL's approach.