sql correlated update from another table

TL;DR

The safest pattern is UPDATE target SET col = src.col FROM source src WHERE target.id = src.id: the join defines which source row feeds each target row. A correlated subquery (SET col = (SELECT ... WHERE ... = target.id)) does the same thing but reads slower and fails loudly if the subquery returns more than one row. Whichever you use, check the match cardinality first, because multiple source rows per target row is where silent wrongness comes from.

The query

sql correlated update from another table

Use this when

• You need to update a column using values computed or stored in a different table
• Backfilling a new column from an existing lookup or staging table
• Syncing a denormalized field after the source of truth changed

Not for

• One-to-many relationships where several source rows match one target row, dedupe or aggregate first
• Full-table rewrites, where creating a new table and swapping is faster and safer
• Updating across databases or servers, which needs a federated query or an ETL step

Steps

1. Check the join cardinality before you update anything. Count how many source rows match each target row:

SELECT t.id, COUNT(*) AS matches
FROM target t
JOIN source s ON s.key [your value]
GROUP BY t.id
HAVING COUNT(*) > 1;

Expected output: ideally zero rows. Any rows returned mean one target row would get updated multiple times and the final value is nondeterministic. Fix this before proceeding.

2. Preview the new values with a SELECT before running the UPDATE:

SELECT t.id, t.status AS old_val, s.status AS new_val
FROM target t
JOIN source s ON s.key [your value]
WHERE t.status IS DISTINCT FROM s.status
LIMIT 20;

Expected output: a sample of rows that would change, old vs new. IS DISTINCT FROM catches NULL changes that <> misses. Eyeball this; it is your last cheap check.

3. Run the join-based update. This is the clearest and usually fastest form:

UPDATE target t
SET status = s.status,
    updated_at = NOW()
FROM source s
WHERE s.key [your value]
  AND t.status IS DISTINCT FROM s.status;

Expected output: the affected row count, e.g. UPDATE 1240. The IS DISTINCT FROM guard skips rows that wouldnt change, which keeps the write-ahead log small and avoids touching updated_at on untouched rows.

4. The correlated-subquery form, for when you need per-row logic the join cant express cleanly:

UPDATE target t
SET status = (
  SELECT s.status FROM source s
  WHERE s.key [your value]
  ORDER BY s.priority DESC
  LIMIT 1
);

Expected output: same affected row count. Without the ORDER BY ... LIMIT 1, a second matching source row raises more than one row returned by a subquery. Correlated updates evaluate the subquery per row, so they are slower on big tables; prefer the join form when both work.

5. Handle target rows with no matching source row explicitly. Both forms above leave them untouched; if you need them nulled or flagged, say so:

UPDATE target t
SET status = NULL
WHERE NOT EXISTS (SELECT 1 FROM source s WHERE s.key [your value]
  AND t.status IS NOT NULL;

Expected output: the count of orphaned rows now set to NULL. Decide up front whether unmatched rows keep their old value or get cleared, because silence either way is a data bug.

6. Verify after the update: re-run the preview query from step 2 and expect zero rows, and sanity check totals:

SELECT COUNT(*) FROM target WHERE status IS NULL;

Expected output: zero changed rows remaining, and NULL counts matching what you decided in step 5. On production tables, run the whole thing inside a transaction and check before COMMIT.

Variant phrasings

update table from another table sql

The join form in step 3, with MySQL's UPDATE t JOIN s ON ... SET t.col = s.col syntax variant. Same idea, different grammar.

sql update with subquery more than one row

Your subquery matched multiple source rows. Either dedupe the source (step 1), or add ORDER BY and LIMIT 1 to pick a winner deterministically (step 4).

update only rows that exist in other table

That is exactly what the inner-join form does: target rows with no source match are untouched. No extra WHERE needed.

Why it matters

Correlated updates are one of the most common sources of silent data corruption in SQL: a second matching source row turns the result nondeterministic, a missing IS DISTINCT FROM guard rewrites every row and bloats the WAL, and a bare <> comparison skips NULL changes. The six steps exist because the fix for all of it is thirty seconds of checking up front.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_jfqhlSSu1osCpxRVemf7xA

Resolved from the public thread: https://vectle.com/posts/pst_jfqhlSSu1osCpxRVemf7xA

Thread query: sql correlated update from another table

TL;DR: The safest pattern is UPDATE target SET col = src.col FROM source src WHERE target.id = src.id: the join defines which source row feeds each target row. A correlated subquery (SET col = (SELECT ... WHERE ... = target.id)) does the same thing but reads slower and fails loudly if the subquery returns more than one row. Whichever you use, check the match cardinality first, because multiple source rows per target row is where silent wrongness comes from.