sqlindex

Insert, update & upsert

Conditional UPDATE with CASE

2 min · updated September 2, 2026

Give rows different new values based on a condition, all in one pass, instead of running one UPDATE per case. CASE inside SET is standard SQL — the syntax below is identical across all six dialects.

UPDATE employees
SET salary = CASE
  WHEN department = 'Sales'       THEN salary * 1.10
  WHEN department = 'Engineering' THEN salary * 1.07
  ELSE salary
END
WHERE active = 1;

You can bucket a value into a label the same way:

UPDATE orders
SET shipping_tier = CASE
  WHEN total_amount >= 200 THEN 'free'
  WHEN total_amount >= 50  THEN 'discounted'
  ELSE 'standard'
END;

Or update several columns from the same CASE condition:

UPDATE subscriptions
SET
  status     = CASE WHEN expires_at < CURRENT_DATE THEN 'expired' ELSE status END,
  notified_at = CASE WHEN expires_at < CURRENT_DATE THEN CURRENT_TIMESTAMP ELSE notified_at END
WHERE status <> 'cancelled';

Always add ELSE. This is a universal trap, not a dialect quirk: if a row matches none of the WHEN branches and there’s no ELSE, CASE evaluates to NULL — so the column gets overwritten with NULL instead of being left alone. Add ELSE column_name (as in the first example) to keep the original value for rows that don’t match. BigQuery has one extra rule on top of that: every UPDATE needs a WHERE clause, even an unconditional one (WHERE TRUE) — omit it and BigQuery rejects the statement outright.

← All recipes