How do you update the rows of one table using values read from another table?
You write the update with a join, so that the new value can be read from the other table.
There is one thing that makes this different from an ordinary update. The condition on the other table has to be part of the join, and not part of the where clause. A filter in the where clause on the other table throws away the rows where there is no match, and then the update changes nothing at all for those rows.
the spellings:
MySQL UPDATE t1 JOIN t2 ON ... SET t1.col = t2.col WHERE ...
PostgreSQL, SQLite UPDATE t1 SET col = t2.col FROM t2 WHERE ...
SQL Server UPDATE t1 SET col = t2.col FROM t1 JOIN t2 ON ... WHERE ...
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
price_cents INTEGER NOT NULL
);
CREATE TABLE price_list (
product_id INTEGER NOT NULL,
list_price_cents INTEGER NOT NULL
);
INSERT INTO products (id, name, price_cents) VALUES (1, 'Notebook', 400);
INSERT INTO price_list (product_id, list_price_cents) VALUES (1, 450);
-- The PostgreSQL and SQLite spelling.
UPDATE products
SET price_cents = price_list.list_price_cents
FROM price_list
WHERE products.id = price_list.product_id;
So what does that do? It finds the rows of the products table that have a match in the price list, and it sets the price to the value from the price list. The rows with no match are left alone.
The next question in this topic asks for a version that copies a value the other way round.