# MERGE and Upserts — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/m-merge

> Insert, update or delete in one statement.

## WHEN MATCHED and WHEN NOT MATCHED

The SQL-standard `MERGE` (PostgreSQL 15 and later) compares a source with a target and, per row, updates, deletes or inserts according to `WHEN MATCHED` and `WHEN NOT MATCHED` clauses with optional extra conditions. It suits batch synchronisation such as applying a delivery to stock. For single-row concurrent upserts, PostgreSQL's `INSERT ... ON CONFLICT DO UPDATE` is simpler and handles concurrent inserts atomically.

## Writes you can trust

MERGE, joined updates and transactions make data changes precise and reversible until committed.

![Three ideas: MERGE, UPDATE ... FROM and DELETE ... USING, safe workflows.](assets/figures/sql-deep-dive/section-7-map.svg) — Figure 7.1 — MERGE, joined updates and safe changes.

## Applying a delivery to stock, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Pen is topped up to 150, lamp (0 in stock and 0 delivered) is deleted, desk is inserted and mug is untouched.

```sql
MERGE INTO stock s
USING delivery d ON s.product = d.product
WHEN MATCHED AND d.qty = 0 AND s.qty = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET qty = s.qty + d.qty
WHEN NOT MATCHED THEN INSERT (product, qty) VALUES (d.product, d.qty);

SELECT * FROM stock ORDER BY product;
```

Output:

```
 product | qty 
---------+-----
 desk    |   3
 mug     |   5
 pen     | 150
(3 rows)
```

## Order WHEN clauses from specific to general

The first matching WHEN clause wins, so put the conditional DELETE before the general UPDATE.

**Quiz:** Which PostgreSQL feature is simplest for a concurrent single-row upsert?

- [ ] CREATE VIEW
- [ ] A SELECT then INSERT in application code
- [ ] TRUNCATE
- [x] INSERT ... ON CONFLICT DO UPDATE

*Answer:* INSERT ... ON CONFLICT DO UPDATE. Atomic with respect to concurrent inserts.