# Deep Dive — MERGE

> One pass over the source, one hash join to the target, and every row is inserted, updated or deleted — no loop, no "does it exist" check.

Used in: [Chapter 1 — SQL First](../course-plan.md) · Appendix entry: [MERGE](../appendix.md#merge) · Related: [INSERT SELECT](insert-select.md), [UPDATE WHERE EXISTS](update-where-exists.md), [FORALL](forall.md), [APPEND HINT](append-hint.md)

---

## 1. What It Is and When

`MERGE` is a conditional `INSERT`/`UPDATE`/`DELETE` driven by a join between a source (any query) and a target table. The optimizer treats it like a join: it reads the source once, joins it to the target (usually a hash join on large sets) and applies the matching branch to each row.

Use it whenever the loop would be "for each source row: check if the target row exists, then update or insert". That loop is the single most common PL/SQL anti-pattern on large tables, and `MERGE` replaces it with one statement.

## 2. Syntax

```sql
MERGE [hint] INTO <target> [t_alias]
USING { <table> | <view> | (<subquery>) } [s_alias]
ON    (<join_condition>)
[WHEN MATCHED THEN
   UPDATE SET <col> = <expr> [, ...]
   [WHERE <update_condition>]
   [DELETE WHERE <delete_condition>]]
[WHEN NOT MATCHED THEN
   INSERT [(<cols>)] VALUES (<exprs>)
   [WHERE <insert_condition>]]
[LOG ERRORS [INTO <err_table>] [(<tag>)] [REJECT LIMIT { <n> | UNLIMITED }]];
```

- At least one of `WHEN MATCHED` / `WHEN NOT MATCHED` is required; either can be omitted.
- `DELETE WHERE` applies **after** the `UPDATE SET` on the same row, and only to rows that matched.
- Columns referenced in `ON` cannot be updated (`ORA-38104`).
- `SQL%ROWCOUNT` after a `MERGE` = inserted + updated + deleted rows, combined.
- Hints: `/*+ APPEND */` on the `INSERT` branch gives direct-path for the inserted rows; `/*+ PARALLEL */` parallelizes the whole statement (with `ENABLE PARALLEL DML`).

## 3. Examples on the Course Schema

### 3a. Update only — replace the classic loop

```sql
MERGE INTO customers c
USING (SELECT customer_id, MAX(order_date) AS last_dt
       FROM   orders
       GROUP  BY customer_id) o
ON    (c.customer_id = o.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.last_order_date = o.last_dt
  WHERE  c.last_order_date IS NULL OR c.last_order_date < o.last_dt;   -- touch only what changes
```

The `WHERE` on the `UPDATE` branch avoids rewriting rows that already hold the value — less redo, fewer index updates, no pointless trigger firing.

### 3b. Full upsert from a staging table (daily customer feed)

```sql
MERGE INTO customers c
USING customers_stage s
ON    (c.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.status = s.status,
             c.tier   = s.tier
WHEN NOT MATCHED THEN
  INSERT (customer_id, status, tier, last_order_date)
  VALUES (s.customer_id, s.status, s.tier, NULL);
```

### 3c. Update + delete in one statement

Cancelled customers with no orders in two years are removed; the rest are updated:

```sql
MERGE INTO customers c
USING (SELECT customer_id, status,
              (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = s.customer_id) AS last_dt
       FROM   customers_stage s) s
ON    (c.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.status = s.status
  DELETE WHERE s.status = 'CANCELLED'
           AND (s.last_dt IS NULL OR s.last_dt < ADD_MONTHS(SYSDATE, -24));
```

### 3d. MERGE inside FORALL — when the source is a PL/SQL collection

```sql
DECLARE
  TYPE t_ids   IS TABLE OF customers.customer_id%TYPE;
  TYPE t_dates IS TABLE OF DATE;
  l_ids   t_ids;
  l_dates t_dates;
BEGIN
  -- l_ids / l_dates filled by earlier PL/SQL logic
  FORALL i IN 1 .. l_ids.COUNT
    MERGE INTO customers c
    USING (SELECT l_ids(i) AS customer_id, l_dates(i) AS last_dt FROM dual) s
    ON    (c.customer_id = s.customer_id)
    WHEN MATCHED THEN
      UPDATE SET c.last_order_date = s.last_dt
    WHEN NOT MATCHED THEN
      INSERT (customer_id, status, last_order_date)
      VALUES (s.customer_id, 'NEW', s.last_dt);
END;
```

One statement per batch, not per row. If the collection is large, a global temporary table + a single `MERGE` is faster still.

### 3e. Bad rows must not stop the load — LOG ERRORS

```sql
-- once:
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('CUSTOMERS');       -- creates ERR$_CUSTOMERS

MERGE INTO customers c
USING customers_stage s
ON    (c.customer_id = s.customer_id)
WHEN MATCHED THEN     UPDATE SET c.tier = s.tier
WHEN NOT MATCHED THEN INSERT (customer_id, status, tier) VALUES (s.customer_id, s.status, s.tier)
LOG ERRORS INTO err$_customers ('daily feed ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD')) REJECT LIMIT UNLIMITED;

SELECT ora_err_number$, ora_err_mesg$, customer_id FROM err$_customers;
```

Rows that violate a constraint or fail a conversion go to the error table with the ORA message; the rest are committed. This is the set-based equivalent of [SAVE EXCEPTIONS](save-exceptions.md).

## 4. What Breaks at Scale

- **The source must be unique on the `ON` key.** Two source rows for one target row raise `ORA-30926: unable to get a stable set of rows in the source tables`. On a 500M-row source this shows up after minutes of work. Deduplicate in the `USING` subquery (`GROUP BY`, or `ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) = 1`).
- **Index on the join key.** With a small source and a huge target the optimizer wants a nested loop through the target's index on the `ON` columns. With a big source it wants a hash join and a full scan of the target — both fine, as long as statistics are current. Check the plan ([DBMS_XPLAN](dbms_xplan.md)).
- **Undo and redo.** `MERGE` is conventional DML: every updated row generates undo and redo. A 40M-row update is one transaction — size undo accordingly, or drive the `MERGE` in chunks with [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md).
- **Direct path applies only to the insert branch** (`MERGE /*+ APPEND */`), and only with the usual direct-path restrictions.

## 5. Pitfalls

1. `ORA-30926` — duplicates in the source. Always deduplicate a feed before merging it.
2. `ORA-38104` — trying to `UPDATE SET` a column used in `ON`. Restructure: join on a stable key, update the other columns.
3. Forgetting the `WHERE` on the `UPDATE` branch on a daily feed — every matched row is rewritten every day whether it changed or not. On 5M customers that is 5M redo records per night for nothing.
4. `DELETE WHERE` is evaluated on the **post-update** values of the row.
5. Triggers fire per affected row exactly as they would for the equivalent `INSERT`/`UPDATE`/`DELETE`.
6. `SQL%ROWCOUNT` cannot tell you how many were inserted vs updated. If you need the split, count the source vs the pre-existing matches before the `MERGE`, or use `LOG ERRORS`-style tagging.
7. A correlated subquery in the `USING` clause (as in 3c) runs per source row. On a large source, join instead.

## 6. Related

- [INSERT SELECT](insert-select.md) — when there is nothing to match against.
- [UPDATE WHERE EXISTS](update-where-exists.md) — the update-only alternative; `MERGE` reads better once you also need values from the source.
- [FORALL](forall.md) — when the source lives in PL/SQL memory.
- [APPEND HINT](append-hint.md) — direct-path for the insert branch.