P16 · SQL & Querying
SQL MERGE and upserts — the duplicate-source trap
How SQL upserts and MERGE work — and the trap where two source rows share one key, so the write is undefined and quietly applies just one.
Start with the basics
Data modification is the write side of SQL: inserting rows, updating them, deleting them, and the combined 'upsert' that updates a row if it exists and inserts it if not.
MERGE is the usual upsert tool: you give it a source and a target, match on a key, and say what to do when a row matches and when it doesn't.
It reads clean, and it is — as long as the source has exactly one row per target key. The moment the source has two rows for one key, the write is on shaky ground.
One that works
| sku | qty |
|---|---|
| A | 10 |
UPDATE stock
SET qty = qty + 5
WHERE sku = 'A';
SELECT sku, qty FROM stock;| sku | qty |
|---|---|
| A | 15 |
A single, well-scoped write lands a known state — qty goes from 10 to 15.
An upsert updates a row if it exists and inserts it if not — but only if the source has one row per target key.
When to use it
- Reach for inserts, updates, deletes, and upserts whenever you change stored data, not just read it.
- For an upsert, make sure the source is one row per key first.
- Design a write to be safe to run twice — the same run should land the same state.
That one condition — one row per key — is where a MERGE goes wrong. Feed it a source with two rows for the same key and the write becomes undefined: some engines error, some silently apply just one. Here is the day that bites.
See it happen
The write that applies half your updates — a duplicated key
You apply two stock deltas for one SKU with a MERGE. Watch it silently apply just one of them, and report success.
| sku | qty |
|---|---|
| A | 10 |
| sku | delta |
|---|---|
| A | 5 |
| A | 3 |
MERGE on a duplicated key — applies one
MERGE INTO stock
USING deltas ON stock.sku = deltas.sku
WHEN MATCHED THEN
UPDATE SET qty = stock.qty + deltas.delta;| sku | qty |
|---|---|
| A | 15 |
pre-aggregate the source — applies both
MERGE INTO stock USING (
SELECT sku, SUM(delta) AS delta
FROM deltas GROUP BY sku
) d ON stock.sku = d.sku
WHEN MATCHED THEN
UPDATE SET qty = stock.qty + d.delta;| sku | qty |
|---|---|
| A | 18 |
The engine applied one of the two updates and reported success — 15, not 18. Pre-aggregating the source to one row per key applies both.
Walk the full example in the unit →The rest of the family
More ways a write goes wrong
Run the same upsert twice and it should land the same state both times — that is what makes a load safe to retry.
Scoping a delete or update through a join means the join condition decides which rows die; get it wrong and you remove too much.
An insert-select lines columns up by position, not name, so a reordered SELECT silently loads values into the wrong columns.
Whether a half-finished multi-statement batch is visible depends on where the transaction begins and ends.
TRUNCATE clears the whole table fast; DELETE removes only the rows you filter — and whether TRUNCATE respects a transaction is engine-specific.
Marking rows deleted with a timestamp means every query must filter them out — and that filter is an is-missing test, never an equals-null.
Self-check
How to spot it in your own SQL
- A MERGE or upsert reports success but the numbers are short.
- Your source table can have more than one row per key.
- Re-running a load changes the result instead of leaving it stable.
- An INSERT ... SELECT loaded values into the wrong columns.
What people miss
Writes are where 'it ran fine' hides the worst bugs, because a MERGE with a duplicated source is undefined — one database errors loudly, another applies half the update and reports success.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why does my MERGE only apply some of the updates?
Your source has more than one row for a target key, which is undefined for MERGE — some engines error, others apply just one of the matching rows.
Pre-aggregate the source to exactly one row per key before the MERGE.
from the unit →How do I make my load safe to re-run?
Write it so running it twice lands the same state — an upsert keyed on a stable key does this, where a blind insert would duplicate rows.
Test it by actually running it twice and comparing.
from the unit →Why did my INSERT ... SELECT load the wrong columns?
INSERT ... SELECT matches columns by position, not by name, so a reordered SELECT quietly loads values into the wrong columns.
List the target columns explicitly and keep the SELECT in the same order.
from the unit →Why did my UPDATE with a join change too many rows?
When you scope an update or delete through a join, the join condition decides which rows are affected — a loose condition hits more rows than intended.
Tighten the join predicate and preview the affected rows with a SELECT first.
from the unit →That’s the free taste