Deep Dive — UPDATE WHERE EXISTS

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":

Once you need values from the source, MERGE reads better and has no key-preservation rules.

2. Syntax

-- 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

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

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

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

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

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 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 — the semi-join itself.
• MERGE — the general form.
• DBMS_PARALLEL_EXECUTE — chunking a huge update.
• DBMS_XPLAN — spotting the FILTER.