Update a table from another table

Copy values across tables with a join, in MySQL, MariaDB and PostgreSQL.

SNP 123 #952 Filed 2 months ago sql

MySQL and MariaDB join in the UPDATE itself:

sql

UPDATE orders o
JOIN customers c ON c.id = o.customer_id
SET o.country = c.country
WHERE o.country IS NULL;

PostgreSQL (and SQLite 3.33+) use FROM:

sql

UPDATE orders o
SET country = c.country
FROM customers c
WHERE c.id = o.customer_id
  AND o.country IS NULL;

Both beat the correlated subquery version, which runs the lookup once per row. As always, run it as a SELECT with the same join first and see how many rows you're about to change.