When Does SQL MERGE Update or Insert Rows?

Preface

This document is for people who understand basic SQL but are confused about exactly when a MERGE statement updates an existing row, inserts a new row, or does nothing, especially when the source data comes from a USING subquery.

It uses Oracle-style MERGE examples to explain the basic update/insert model. Other databases may differ.

Basic Structure of a MERGE Statement

A typical Oracle-style MERGE statement looks like this:

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

Each part has a different responsibility.

MERGE INTO target_table t names the table that may be changed.

USING source_table s provides the source rows. The source can be a table, view, or subquery.

ON (t.id = s.id) decides whether each source row has a matching target row.

WHEN MATCHED THEN UPDATE describes what can happen when a matching target row exists.

WHEN NOT MATCHED THEN INSERT describes what can happen when no matching target row exists.

The ON condition is the boundary between update and insert. It does not update or insert by itself. It classifies each source row so the correct branch can run.

The Basic Rule

After the statement structure is clear, the key question is:

When does a MERGE update, when does it insert, and when does it do nothing?

The key is that a MERGE decision happens in stages:

USING source rows -> ON match test -> WHEN MATCHED or WHEN NOT MATCHED -> branch condition -> actual action.

A basic MERGE statement does not ask, “Does the target table have data?”

It asks a narrower question for each source row:

Does this source row match a target row according to the ON condition?

If the answer is yes, the source row goes to the WHEN MATCHED branch and may update the matching target row.

If the answer is no, the source row goes to the WHEN NOT MATCHED branch and may insert a new target row.

If the source row is filtered out before the MERGE, or if the chosen branch has an extra condition that is false, the row causes no action.

So the update and insert criteria are not:

target table has data -> update

and:

target table has no data -> insert

The criteria are:

source row matches a target row by ON -> possible update

and:

source row does not match any target row by ON -> possible insert

Conceptual Pseudocode

A useful mental model is to read MERGE as if it loops over the rows produced by USING.

This is not a claim about how the database engine physically executes the statement. It is a way to reason about the result.

source_rows = result of the USING clause
 
for each source_row in source_rows:
    matching_target_row = target row where the ON condition is true
 
    if matching_target_row exists:
        if the UPDATE branch condition is true:
            update matching_target_row
        else:
            do nothing
    else:
        if the INSERT branch condition is true:
            insert a new target row from source_row
        else:
            do nothing

This model makes two boundaries clear.

First, rows not returned by USING never enter the loop.

Second, target-only rows do not enter the loop either, because the loop starts from source rows.

Step 1: USING Produces the Source Rows

The USING clause defines the input row set for the MERGE.

A source row does not have to come from every row in a physical source table. It can come from a subquery:

MERGE INTO target_table t
USING (
    SELECT id, name
    FROM source_table
    WHERE active_yn = 'Y'
) s
ON (t.id = s.id)
...

In this example, the MERGE does not evaluate every row in source_table.

It evaluates only rows returned by the USING subquery:

SELECT id, name
FROM source_table
WHERE active_yn = 'Y'

Rows filtered out by the subquery are not update candidates and not insert candidates. They are not source rows for this MERGE.

So when reading a MERGE, first ask:

What rows does the USING clause produce?

Only after that should you ask:

For each of those source rows, does the ON condition find a matching target row?

This separation prevents a common confusion: the subquery controls which rows enter the MERGE, but it does not by itself decide update versus insert.

Step 2: ON Chooses MATCHED or NOT MATCHED

MATCHED means that a source row found a corresponding row in the target table using the ON condition.

NOT MATCHED means that a source row did not find a corresponding row in the target table.

Suppose the target table contains this data:

id	name
1	Alice
2	Bob
3	Chris

And the source rows produced by USING are:

id	name
2	Bobby
3	Chris
4	Diana

If the ON condition is:

ON (t.id = s.id)

then each source row is classified like this:

source id	Matching target row?	Classification
2	Yes	MATCHED
3	Yes	MATCHED
4	No	NOT MATCHED

id = 2 and id = 3 are update candidates because matching target rows exist.

id = 4 is an insert candidate because no matching target row exists.

This classification is the moment where the update/insert path is chosen.

Step 3: MATCHED Can Update

A matched row can update the matching target row.

In the simplest case:

WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name

every source row classified as MATCHED is updated.

Using the previous example, id = 2 updates the target name from Bob to Bobby. id = 3 also reaches the update branch, even if the value is already Chris.

But MATCHED does not always mean updated. The update branch can have an extra condition:

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name
    WHERE t.name <> s.name
WHEN NOT MATCHED THEN
    INSERT (id, name)
    VALUES (s.id, s.name);

Here, a row must satisfy two conditions to be updated:

It must be matched by the ON condition.
It must satisfy the update WHERE condition.

The update condition is:

t.name <> s.name

This comparison assumes ordinary non-null values. If NULL values matter, use the database’s correct null-safe comparison pattern.

With this target data:

id	name
2	Bob
3	Chris

and these source rows:

id	name
2	Bobby
3	Chris

both source rows are MATCHED, but only one is updated:

source id	MATCHED?	Values different?	Actual action
2	Yes	Yes	UPDATE
3	Yes	No	No action

So the number of matched rows and the number of updated rows can be different.

Step 4: NOT MATCHED Can Insert

A not matched source row can insert a new target row.

In the simplest case:

WHEN NOT MATCHED THEN
    INSERT (id, name)
    VALUES (s.id, s.name)

every source row classified as NOT MATCHED is inserted.

Using the earlier example, id = 4 is inserted because no target row has id = 4.

After the MERGE, the target table may become:

id	name
1	Alice
2	Bobby
3	Chris
4	Diana

But NOT MATCHED does not always mean inserted. The insert branch can also have an extra condition:

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name
WHEN NOT MATCHED THEN
    INSERT (id, name, active_yn)
    VALUES (s.id, s.name, s.active_yn)
    WHERE s.active_yn = 'Y';

Here, a row must satisfy two conditions to be inserted:

It must be not matched by the ON condition.
It must satisfy the insert WHERE condition.

Suppose the source rows are:

id	name	active_yn
4	Diana	Y
5	Eric	N

And neither id = 4 nor id = 5 exists in the target table.

Both source rows are NOT MATCHED, but only one is inserted:

source id	NOT MATCHED?	Insert condition satisfied?	Actual action
4	Yes	Yes	INSERT
5	Yes	No	No action

So the number of not matched rows and the number of inserted rows can be different.

When MERGE Does Nothing

A MERGE can leave a row unchanged for several different reasons.

Situation	Why no action happens
A source table row is filtered out by `USING`	It never becomes a source row for this `MERGE`
A source row is `MATCHED`, but the update condition is false	It reaches the update branch but is skipped
A source row is `NOT MATCHED`, but the insert condition is false	It reaches the insert branch but is skipped
A target row exists but no source row matches it	A basic source-side `MERGE` does not process it as a work item

The last case is especially easy to confuse.

A common misunderstanding is to think that WHEN NOT MATCHED means:

target rows that do not exist in the source.

That is usually not what WHEN NOT MATCHED means in a basic Oracle-style MERGE.

The statement normally starts from the source rows. For each source row, it checks whether a matching target row exists.

So this target-only row does not automatically cause anything to happen:

target id	source id
1	none

If a row exists only in the target table but not in the source data, a basic MERGE usually leaves it unchanged.

How to Calculate Update and Insert Counts

The first count to check is the number of rows produced by USING, not the number of rows in the physical source table.

When USING is a subquery, that means the final row count returned by the subquery after its WHERE clause is applied.

If the MERGE has an unconditional update branch and an unconditional insert branch, every source row produced by USING becomes either an update or an insert.

In that simple subquery case:

UPDATE count + INSERT count
= rows returned by the USING subquery

That equality stops being guaranteed when the update or insert branch has an extra WHERE condition. In that case, some source rows may be classified as MATCHED or NOT MATCHED but still cause no action.

Count in this order:

Count the rows produced by USING.
Classify those source rows with the ON condition.
Apply the update condition to matched rows.
Apply the insert condition to not matched rows.

The update count is:

source rows matched by the ON condition
and satisfying the UPDATE condition

The insert count is:

source rows not matched by the ON condition
and satisfying the INSERT condition

In the basic update/insert model, each source row has one of these outcomes:

Source row state	Possible result
Matched and update condition is true	UPDATE
Not matched and insert condition is true	INSERT
Matched or not matched, but branch condition is false	No action

Rows not produced by USING are outside this table because they are not source rows for the MERGE.

The ON Condition Must Identify One Logical Target Row

The ON condition should usually be based on a primary key or a unique business key.

For example:

ON (t.customer_id = s.customer_id)

or:

ON (t.order_no = s.order_no)

The ON condition answers this question:

Which target row corresponds to this source row?

If the ON condition is too broad, the wrong row may be updated.

If the source data contains duplicate keys, one target row may match multiple source rows. In some databases, this can cause an error. In Oracle, for example, this can lead to an unstable set of rows error.

That is why source data is often cleaned before using MERGE.

Common techniques include:

ROW_NUMBER() OVER (...)

or:

GROUP BY

or filtering the source data so that only one row exists for each key.

Oracle DELETE WHERE Is Still Part of the Update Branch

This document focuses on the update/insert decision, but Oracle also supports a DELETE WHERE clause inside the update branch.

For example:

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET
        t.status = s.status
    DELETE WHERE t.status = 'DELETE_ME'
WHEN NOT MATCHED THEN
    INSERT (id, status)
    VALUES (s.id, s.status);

This DELETE WHERE clause is not a filter over the whole target table.

It is also not applied to rows inserted by the WHEN NOT MATCHED branch.

It applies only to target rows that were updated by the WHEN MATCHED update branch.

If the update branch has its own WHERE condition and that condition is false, the row is skipped by the update and is not deleted by that branch’s DELETE WHERE.

The order is:

source row -> ON condition -> MATCHED -> UPDATE -> DELETE WHERE -> possible DELETE

The delete condition checks the updated target row values, not the original target row values before the update.

So if a target row already satisfies the delete condition but is not matched and updated by this MERGE, this DELETE WHERE clause does not delete it.

Likewise, if a row is newly inserted by the WHEN NOT MATCHED branch, this DELETE WHERE clause does not delete it even if the inserted values satisfy the delete condition.

MERGE Is Not Full Synchronization

A basic MERGE statement is not necessarily a full synchronization between two tables.

It usually means:

Apply source rows to the target table.

It does not automatically mean:

Make the target table exactly the same as the source table.

For example, if the target table has a row that is missing from the source data, a basic MERGE statement does not automatically delete it.

To delete target-only rows, you need extra logic, and the exact syntax depends on the database system.

So it is better to think of MERGE as a controlled data application mechanism, not as a magic table synchronization command.

Summary

The USING clause produces the source rows.

The ON condition classifies each source row as MATCHED or NOT MATCHED.

A MATCHED source row can update the matching target row, but only if the update branch condition allows it.

A NOT MATCHED source row can insert a new target row, but only if the insert branch condition allows it.

When USING is a subquery and both update and insert branches are unconditional, the total update-plus-insert count equals the number of rows returned by the USING subquery.

Rows filtered out by USING, rows skipped by branch conditions, and target-only rows usually cause no action in a basic source-side MERGE.

The durable rule is:

USING decides which source rows exist for this MERGE. ON decides whether each source row is matched. The selected branch decides whether the row is actually updated, inserted, or left unchanged.

Insight Vault

Browse