UPDATE a table from a join

Copy values from one table into another based on a match. The clause that does the join differs by dialect.

-- PostgreSQL, DuckDB, BigQuery, Snowflake  (UPDATE ... FROM)
UPDATE orders AS o
SET status = c.default_status
FROM customers AS c
WHERE o.customer_id = c.id;

-- MySQL  (UPDATE ... JOIN — the SET comes after the join)
UPDATE orders AS o
JOIN customers AS c ON o.customer_id = c.id
SET o.status = c.default_status;

-- SQLite 3.33+  (also supports UPDATE ... FROM)
UPDATE orders
SET status = c.default_status
FROM customers AS c
WHERE orders.customer_id = c.id;

Portable everywhere (older SQLite, strict engines) — a correlated subquery:

UPDATE orders
SET status = (SELECT c.default_status FROM customers c WHERE c.id = orders.customer_id)
WHERE customer_id IN (SELECT id FROM customers);

Traps.

1. In the UPDATE ... FROM form, don’t repeat the target table in FROM — list only the source tables there, or Postgres makes a second, unjoined copy and updates every row.
2. BigQuery requires a WHERE on every UPDATE (use WHERE TRUE for an intentional full-table update).
3. The correlated-subquery form needs the WHERE ... IN (...) guard, or unmatched rows get set to NULL.