# Deep Dive — UPDATE WHERE EXISTS

> A correlated update touches exactly the rows that have a match — and the optimizer turns it into a join, not a loop.

Used in: [Chapter 1 — SQL First](../course-plan.md) · Appendix entry: [UPDATE WHERE EXISTS](../appendix.md#update-where-exists) · Related: [EXISTS](exists.md), [MERGE](merge.md), [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md)

---

## 1. What It Is and When

An `UPDATE` whose `SET` and/or `WHERE` refer to another table through a correlated subquery. Three forms exist; all replace "loop over source, update target per row":

| Form | Use when |
| --- | --- |
| `UPDATE t SET col = <const> WHERE EXISTS (...)` | The new value does not come from the other table. |
| `UPDATE t SET col = (SELECT ... FROM s WHERE s.k = t.k) WHERE EXISTS (...)` | The value comes from the other table. |
| `UPDATE (SELECT t.col, s.val FROM t JOIN s ON ...) SET col = val` | Updatable join view — often the fastest, needs a key-preserved join. |

Once you need values from the source, [MERGE](merge.md) reads better and has no key-preservation rules.

## 2. Syntax

```sql
-- constant value, membership test
UPDATE <target> t
SET    <col> = <expr>
WHERE  EXISTS (SELECT 1 FROM <src> s WHERE s.<key> = t.<key> [AND ...]);

-- value from the source (scalar subquery, must return <= 1 row)
UPDATE <target> t
SET    (<col1>, <col2>) = (SELECT s.<a>, s.<b> FROM <src> s WHERE s.<key> = t.<key>)
WHERE  EXISTS            (SELECT 1           FROM <src> s WHERE s.<key> = t.<key>);

-- updatable join view (s.key must be unique: "key-preserved")
UPDATE (SELECT t.<col> AS old_val, s.<a> AS new_val
        FROM   <target> t JOIN <src> s ON s.<key> = t.<key>)
SET    old_val = new_val;
```

## 3. Examples on the Course Schema

### 3a. Membership — activate customers with a recent order

```sql
UPDATE customers c
SET    c.status = 'ACTIVE'
WHERE  c.status <> 'ACTIVE'
AND    EXISTS (SELECT 1 FROM orders o
               WHERE  o.customer_id = c.customer_id
               AND    o.order_date >= ADD_MONTHS(SYSDATE, -12));
```

The extra `c.status <> 'ACTIVE'` keeps already-active rows out of the update — no redo, no index maintenance, no trigger for rows that would not change.

### 3b. Value from the source — and why `WHERE EXISTS` is mandatory

```sql
UPDATE customers c
SET    c.last_order_date = (SELECT MAX(o.order_date) FROM orders o
                            WHERE  o.customer_id = c.customer_id)
WHERE  EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
```

Without the `WHERE EXISTS`, every customer with **no** orders gets `last_order_date = NULL` — the subquery returns no row, which becomes NULL. 4M rows silently wiped. The `WHERE EXISTS` restricts the update to rows that have a value.

### 3c. Several columns in one subquery

```sql
UPDATE customers c
SET    (c.status, c.tier) = (SELECT s.status, s.tier
                             FROM   customers_stage s
                             WHERE  s.customer_id = c.customer_id)
WHERE  EXISTS (SELECT 1 FROM customers_stage s WHERE s.customer_id = c.customer_id);
```

One subquery, one join — not one subquery per column.

### 3d. Updatable join view — the fastest form

```sql
UPDATE (SELECT c.tier AS cur_tier, s.tier AS new_tier
        FROM   customers c
        JOIN   customers_stage s ON s.customer_id = c.customer_id)
SET    cur_tier = new_tier
WHERE  cur_tier <> new_tier OR cur_tier IS NULL;
```

Requires `customers_stage.customer_id` to be declared unique or primary key (key-preserved), otherwise `ORA-01779: cannot modify a column which maps to a non key-preserved table`. The plan is a plain hash join — no scalar subquery per row.

### 3e. The same thing as a MERGE

```sql
MERGE INTO customers c
USING customers_stage s
ON    (c.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.tier = s.tier
  WHERE  c.tier <> s.tier OR c.tier IS NULL;
```

Same plan as 3d, no key-preservation rule, and `WHEN NOT MATCHED` is one line away if you need it later.

## 4. What Breaks at Scale

- **Subquery unnesting.** Oracle normally unnests the correlated subquery into a semi-join (`HASH JOIN SEMI`). When it cannot (complex subquery, hints, `NO_UNNEST`), the plan shows a `FILTER` step — the subquery runs once per target row. On 5M rows that is 5M index probes. Check the plan; a `FILTER` under an `UPDATE` on a big table is a red flag.
- **Scalar subquery in `SET`.** Also usually unnested, but it must be guaranteed single-row — `MAX()`, or a unique key. `ORA-01427: single-row subquery returns more than one row` after 20 minutes of work is the failure mode.
- **One transaction.** A 5M-row update = 5M undo records held until commit. Fine for millions; for hundreds of millions, chunk with [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md) using a rowid-range predicate on the target.
- **Index maintenance.** Updating an indexed column deletes and reinserts the index entry. Updating the partition key moves the row (`ENABLE ROW MOVEMENT` required, and it is expensive).

## 5. Pitfalls

1. Missing `WHERE EXISTS` on a `SET (subquery)` — every unmatched row becomes NULL.
2. `ORA-01427` — the `SET` subquery returns more than one row. Aggregate or deduplicate.
3. `ORA-01779` — updatable join view on a non-unique source. Add the unique constraint on the stage table, or use `MERGE`.
4. Updating rows to the value they already have — filter them out (`WHERE cur <> new`). NULL-safe comparison needed if the column is nullable: `DECODE(cur, new, 0, 1) = 1` is the compact trick.
5. Two separate correlated subqueries for two columns — twice the work. Use the `(a, b) = (SELECT ...)` form.
6. A `FILTER` operation in the plan on a large target — the subquery was not unnested. Rewrite as `MERGE` or an updatable join view.

## 6. Related

- [EXISTS](exists.md) — the semi-join itself.
- [MERGE](merge.md) — the general form.
- [DBMS_PARALLEL_EXECUTE](dbms_parallel_execute.md) — chunking a huge update.
- [DBMS_XPLAN](dbms_xplan.md) — spotting the `FILTER`.