SQL UPDATE from SELECT: 4 Bulletproof Patterns That Work

You need to backfill a column from another table — copy the new prices over, sync the statuses, fix the emails from the staging import. A plain UPDATE can't see the other table, so you reach for UPDATE-from-SELECT. Then the dialect differences hit.

Postgres wants UPDATE..FROM, MySQL wants UPDATE..JOIN, SQL Server wants its own FROM flavor, and the 'portable' correlated subquery has traps of its own. Pick the wrong shape and you update every row or fail with a cryptic alias error.

There's a safe path. You'll learn four patterns — one per major dialect plus the portable fallback — and the SELECT-first workflow that makes runaway updates structurally impossible.

The SELECT-First Workflow That Prevents Disasters

Every safe UPDATE-from-SELECT starts life as a SELECT. Write SELECT t.id, t.price, s.price FROM products t JOIN price_feed s ON s.sku = t.sku and eyeball the rows: right count, right matches, no duplicates per key.

Check two counts: SELECT count() of the join (expect 18,204, not 4.2M) and SELECT key, count() GROUP BY key HAVING count(*) > 1 to catch multi-matches. Both take seconds and catch the entire incident class above.

Only then convert: swap the SELECT list for UPDATE products SET price = s.price keeping FROM/WHERE identical. Run in a transaction, verify with RETURNING, commit. The workflow is slower by two minutes and safer by three-hour rollbacks.

Pattern 1: PostgreSQL UPDATE..FROM Done Right

Postgres syntax: UPDATE products SET price = s.price FROM price_feed s WHERE s.sku = products.sku AND s.region = 'EU'. The FROM lists sources; the WHERE carries both the join predicate and filters. Forget the predicate and every row matches everything.

Alias discipline matters: UPDATE products AS p SET price = s.price FROM price_feed s WHERE s.sku = p.sku. Never repeat the bare target name in FROM (that's a self-join requiring its own alias) — the classic 'table specified more than once' error.

Add RETURNING id, price for an instant audit trail of touched rows. For multi-match safety, pre-aggregate the source: FROM (SELECT sku, max(price) FROM price_feed GROUP BY sku) s guarantees one row per key.

-- 1. SELECT first: verify matches + counts
SELECT p.id, p.price AS old, s.price AS new
FROM products AS p
JOIN price_feed AS s ON s.sku = p.sku
WHERE s.region = 'EU';

-- 2. Convert to UPDATE (same FROM/WHERE), audit with RETURNING
BEGIN;
UPDATE products AS p
SET price = s.price, updated_at = now()
FROM price_feed AS s
WHERE s.sku = p.sku
  AND s.region = 'EU'
RETURNING p.id, p.price;
-- COMMIT;  -- only after checking the RETURNING output
ROLLBACK; -- practice safely: inspect, then commit for real

Pattern 2: SQL Server UPDATE..FROM with JOINs

SQL Server hangs the UPDATE off an alias and joins in FROM: UPDATE p SET p.price = s.price FROM products p INNER JOIN price_feed s ON s.sku = p.sku WHERE s.region = 'EU'. The alias after UPDATE is load-bearing — UPDATE products with a later p alias errors.

Prefer explicit INNER JOIN over comma joins for readability; both run, but the JOIN form keeps predicates attached to their tables. For top-N guarded updates, the proprietary UPDATE TOP (1000) exists — but batch with WHILE loops and keyset filters instead for restartable migrations.

SQL Server 2008+ also offers MERGE with WHEN MATCHED THEN UPDATE, which adds niet-determinism guards (it errors on multiple source matches instead of silently picking). For sync jobs where duplicates are possible, MERGE's loud failure beats UPDATE's silent pick.

-- SQL Server: alias after UPDATE, JOIN in FROM
BEGIN TRAN;
UPDATE p
SET p.price = s.price,
    p.updated_at = SYSDATETIME()
FROM products AS p
INNER JOIN price_feed AS s ON s.sku = p.sku
WHERE s.region = 'EU';
-- SELECT @@ROWCOUNT;  -- expect 18204, not 4200000
-- COMMIT;  -- after verifying rowcount
-- ROLLBACK;

-- Safer sync alternative: MERGE errors on duplicate matches
-- MERGE INTO products AS p USING price_feed AS s ON s.sku = p.sku
-- WHEN MATCHED AND s.region = 'EU' THEN UPDATE SET p.price = s.price;

Pattern 3: MySQL UPDATE..JOIN (and the 1093 Trap)

MySQL joins before SET: UPDATE products p INNER JOIN price_feed s ON s.sku = p.sku SET p.price = s.price WHERE s.region = 'EU'. Table list first, assignments after — the reverse order of other dialects, and the top syntax-memorization failure.

The infamous ERROR 1093 blocks subqueries selecting from the target table: UPDATE items SET ... WHERE id IN (SELECT id FROM items ...) fails. Fix it MySQL-style with the JOIN form, or wrap the inner query as a derived table (SELECT ... ) AS d to force materialization.

MySQL multi-table UPDATE can touch several tables at once, but don't — one table per statement keeps rowcounts interpretable and rollbacks clean. Batch large updates with LIMIT + keyset loops to avoid 11-minute lock holds.

-- MySQL: JOIN first, SET after
START TRANSACTION;
UPDATE products AS p
INNER JOIN price_feed AS s ON s.sku = p.sku
SET p.price = s.price,
    p.updated_at = NOW()
WHERE s.region = 'EU';
-- SELECT ROW_COUNT();  -- sanity-check before commit
-- COMMIT;
-- ROLLBACK;

-- 1093 workaround: derived table forces materialization
-- UPDATE products AS p
-- INNER JOIN (SELECT sku, MAX(price) AS price FROM price_feed GROUP BY sku) AS s
--   ON s.sku = p.sku
-- SET p.price = s.price;

Pattern 4: Portable Correlated Subquery (Runs Everywhere)

When one migration must run on Postgres, MySQL, and SQLite, use the standard form: UPDATE products SET price = (SELECT s.price FROM price_feed s WHERE s.sku = products.sku) WHERE EXISTS (SELECT 1 FROM price_feed s WHERE s.sku = products.sku AND s.region = 'EU'). Ugly, universal.

Two rules keep it correct: the scalar subquery must return at most one row per target (aggregate or LIMIT 1 with ORDER BY if the source can duplicate), and the EXISTS clause prevents NULL-writes to unmatched rows. Drop EXISTS and every non-matching product gets price = NULL — a second outage flavor.

It's slower than dialect JOINs (correlated execution per row without good indexes), so index the source key (price_feed.sku) first. Portability costs a nested loop; the index keeps it affordable.

Locking, Batching, and Running at Scale

An 18k-row update takes a second; a 4M-row one holds locks for 11 minutes and blocks checkout. Batch by keyset: UPDATE ... WHERE id > $last AND id <= $last + 5000 in a loop, committing per batch. Restartable, lock-friendly, and progress-visible.

Order matters for deadlocks: process keys ascending everywhere so concurrent jobs lock in the same sequence. Avoid updating the indexed join key itself mid-migration — it churns the very index the scan uses.

Schedule right: run backfills in low-traffic windows, announce lock scope in the deploy ticket, and keep the snapshot/rollback command pasted beside the migration. The incident's 3-hour rollback started with 20 minutes just finding the snapshot procedure.