Oracle SQL MERGE Statement and Advanced DML Patterns

MERGE solves the common "upsert" problem — when you have a source of data and need to update matching rows in a target table while inserting rows that do not yet exist, all in a single efficient operation.

This guide covers the full MERGE syntax, the optional DELETE clause inside WHEN MATCHED, how to make WHEN clauses optional, proper use of DEFAULT values, the explicit DEFAULT keyword, and important rules around multi-row DML and constraint checking.

In this post
MERGE syntax and semantics • WHEN MATCHED with optional DELETE • Optional WHEN clauses • DEFAULT values and the DEFAULT keyword • Multi-row DML constraint behavior • Practice questions

1. MERGE — the combined INSERT/UPDATE statement

MERGE solves the "upsert" problem — when you have new data in a staging table and want to update existing rows in the target while inserting new ones. Without MERGE you would need separate UPDATE and INSERT statements with additional logic to decide which rows need which operation.

-- The problem MERGE solves:
-- You have new employee data in a staging table (emp_updates)
-- For each row: if the employee already exists, UPDATE them
--               if they don't exist yet, INSERT them

-- Without MERGE: two separate statements with subquery logic
UPDATE employees SET salary = ...
WHERE  employee_id IN (SELECT employee_id FROM emp_updates);

INSERT INTO employees ...
SELECT * FROM emp_updates
WHERE  employee_id NOT IN (SELECT employee_id FROM employees);

-- With MERGE: one statement handles both cases cleanly

MERGE is DML: Like INSERT, UPDATE, and DELETE, MERGE is a DML statement. It participates in transactions — it can be rolled back until COMMIT is issued.

2. MERGE full syntax

The MERGE syntax has four key parts: the target table, the source, the join condition, and the WHEN clauses.

MERGE INTO employees e                     -- target table
USING      emp_updates u                    -- source table (or subquery)
ON        (e.employee_id = u.employee_id)   -- join condition
WHEN MATCHED THEN                           -- row exists in target
    UPDATE SET
        e.salary        = u.salary,
        e.department_id = u.department_id
WHEN NOT MATCHED THEN                       -- row does not exist in target
    INSERT (employee_id, last_name, salary, department_id)
    VALUES (u.employee_id, u.last_name, u.salary, u.department_id);

Part	Keyword	Purpose
Target	MERGE INTO	The table being updated or inserted into
Source	USING	The data source — a table, view, or subquery
Join condition	ON (...)	How to match source rows to target rows
Matched rows	WHEN MATCHED THEN UPDATE	What to do when source row matches target
Unmatched rows	WHEN NOT MATCHED THEN INSERT	What to do when source row has no match in target

Cannot UPDATE the join column in WHEN MATCHED: The column used in the ON condition cannot be updated in the WHEN MATCHED UPDATE clause. Oracle raises an error if you attempt this.

3. MERGE with WHEN NOT MATCHED

WHEN NOT MATCHED fires for every source row that has no matching row in the target table. You can also add an optional WHERE clause to filter which unmatched rows get inserted.

MERGE INTO employees e
USING      new_hires n
ON        (e.employee_id = n.employee_id)
WHEN NOT MATCHED THEN
    INSERT (employee_id, last_name, email, hire_date, job_id, salary)
    VALUES (n.employee_id, n.last_name, n.email,
             n.hire_date, n.job_id, n.salary)
    WHERE  n.hire_date >= TO_DATE('01-JAN-2024', 'DD-MON-YYYY');
-- The optional WHERE on WHEN NOT MATCHED filters which unmatched rows get inserted

Optional WHERE on WHEN clauses: Both WHEN MATCHED and WHEN NOT MATCHED support their own WHERE clause for fine-grained control.

4. MERGE with WHEN MATCHED — optional DELETE

WHEN MATCHED can include an optional DELETE clause in addition to UPDATE. A row that is matched and updated can also be deleted in the same operation if it satisfies the DELETE condition.

MERGE INTO employees e
USING      emp_changes c
ON        (e.employee_id = c.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = c.new_salary,
               e.job_id = c.new_job_id
    DELETE WHERE (c.status = 'TERMINATED');
-- Step 1: UPDATE is applied to all matched rows
-- Step 2: Of the rows just updated, those where status = 'TERMINATED' are deleted

DELETE condition uses updated values: The DELETE WHERE condition is evaluated after the UPDATE has been applied.

5. Making WHEN clauses optional

Both WHEN MATCHED and WHEN NOT MATCHED are optional. You only need to include the clauses you actually need.

-- UPDATE only — no INSERT for new rows
MERGE INTO employees e
USING      salary_updates u
ON        (e.employee_id = u.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = u.new_salary;
-- Unmatched source rows are silently ignored

-- INSERT only — no UPDATE for existing rows
MERGE INTO employees e
USING      new_hires n
ON        (e.employee_id = n.employee_id)
WHEN NOT MATCHED THEN
    INSERT (employee_id, last_name, salary)
    VALUES (n.employee_id, n.last_name, n.salary);
-- Matched rows are silently ignored

Clauses present	Behaviour
WHEN MATCHED only	Updates matched rows — unmatched source rows ignored
WHEN NOT MATCHED only	Inserts unmatched rows — matched rows ignored
Both clauses	Updates matched, inserts unmatched — full upsert
Neither clause	Syntax error — at least one WHEN clause required

6. DEFAULT values in INSERT and UPDATE

A DEFAULT value defined at the column level is used automatically when a row is inserted without specifying that column.

-- Column defined with DEFAULT
CREATE TABLE orders (
    order_id    NUMBER        PRIMARY KEY,
    order_date  DATE          DEFAULT SYSDATE,
    status      VARCHAR2(10)  DEFAULT 'PENDING'
);

-- DEFAULT is used when column is omitted from INSERT
INSERT INTO orders (order_id)
VALUES (1001);
-- order_date gets SYSDATE, status gets 'PENDING'

-- DEFAULT is NOT used when NULL is explicitly inserted
INSERT INTO orders (order_id, status)
VALUES (1002, NULL);
-- status gets NULL — not 'PENDING'

Explicit NULL bypasses DEFAULT: Writing VALUES (..., NULL) stores NULL. The DEFAULT only applies when the column is completely omitted from the INSERT column list.

7. Explicit DEFAULT keyword in DML

You can use the DEFAULT keyword explicitly in INSERT and UPDATE to force the default value.

-- Using DEFAULT explicitly in INSERT
INSERT INTO orders (order_id, order_date, status)
VALUES (1003, DEFAULT, 'EXPRESS');

-- Using DEFAULT in UPDATE
UPDATE orders
SET    status = DEFAULT
WHERE  order_id = 1003;

8. DEFAULT with NULL — important distinction

There is an important three-way distinction between a column with a DEFAULT, a column with no DEFAULT, and explicitly inserting NULL.

Scenario	Column has DEFAULT 'PENDING'	Column has no DEFAULT
Column omitted from INSERT	Stores 'PENDING'	Stores NULL
INSERT ... VALUES (..., NULL)	Stores NULL	Stores NULL
INSERT ... VALUES (..., DEFAULT)	Stores 'PENDING'	Stores NULL
UPDATE SET col = DEFAULT	Sets to 'PENDING'	Sets to NULL

9. Multi-row DML and constraint checking

When a DML statement affects multiple rows, Oracle checks constraints after all rows have been processed — not row by row.

-- Swapping manager IDs in a self-referencing table
UPDATE employees
SET    manager_id = CASE
                        WHEN manager_id = 100 THEN 101
                        WHEN manager_id = 101 THEN 100
                    END
WHERE  manager_id IN (100, 101);

All or nothing per statement: If any row in a multi-row DML statement violates a constraint, the entire statement is rolled back.

10. Quick reference

Rule	Detail
MERGE INTO ... USING ... ON	Target, source, join condition — all required
WHEN MATCHED — cannot update join column	Raises error if ON column appears in UPDATE SET
WHEN MATCHED DELETE WHERE	Deletes rows after UPDATE — condition uses updated values
Both WHEN clauses are optional	Must have at least one
MERGE is DML	Participates in transactions — can ROLLBACK
Omitting column uses DEFAULT	Column not in INSERT list gets its DEFAULT value
Explicit NULL overrides DEFAULT	VALUES (..., NULL) stores NULL — ignores DEFAULT
DEFAULT keyword in DML	Forces the column's defined default
Multi-row DML is all or nothing	One constraint violation fails the entire statement

11. Practice questions

Q1. A MERGE statement has a WHEN MATCHED THEN UPDATE SET clause. The ON condition uses employee_id. A developer tries to include e.employee_id = u.employee_id in the UPDATE SET. What happens?

A) The employee_id is updated successfully B) Oracle raises an error — the join column cannot be updated in WHEN MATCHED C) The update is silently ignored for the employee_id column only D) The entire MERGE fails only if employee_id values would actually change

Q2. A table has a DEFAULT of 'ACTIVE' on the STATUS column. A developer runs: INSERT INTO orders (order_id, status) VALUES (100, NULL); What value is stored in STATUS?

A) 'ACTIVE' — the DEFAULT is applied B) NULL — explicit NULL overrides the DEFAULT value C) An error is raised D) 'ACTIVE' — Oracle always uses DEFAULT

Q3. A developer runs an INSERT that selects 500 rows from a staging table. Row number 347 violates a UNIQUE constraint. What is the result?

A) The first 346 rows are inserted successfully B) All 500 rows are rejected — a constraint violation fails the entire statement C) Row 347 is skipped and the remaining rows are inserted D) Oracle inserts all rows and logs the violation