Updating Across Tables

There are two portable-ish ways. The first uses a correlated subquery in the SET clause: write the new value as a subquery that looks up the other table, and restrict the WHERE clause to the rows you want to change. The second is UPDATE ... FROM, which PostgreSQL, SQLite and SQL Server all support and which reads more like a join.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  price_cents INTEGER NOT NULL
);

CREATE TABLE price_updates (
  product_id INTEGER PRIMARY KEY,
  new_price_cents INTEGER NOT NULL
);

INSERT INTO products (id, name, price_cents) VALUES
  (1, 'Mechanical Keyboard', 7950),
  (2, 'USB-C Hub',        3200),
  (3, '27-inch Monitor',  24999);

INSERT INTO price_updates (product_id, new_price_cents) VALUES
  (1, 7499);

UPDATE products
SET price_cents = (
  SELECT u.new_price_cents
  FROM price_updates AS u
  WHERE u.product_id = products.id
)
WHERE id IN (SELECT product_id FROM price_updates);

SELECT id, name, price_cents FROM products ORDER BY id;

Three rows come back. The Mechanical Keyboard is now 7499 cents, down from 7950, and the other two are unchanged. The WHERE clause is what keeps the update scoped: it restricts the change to the products that actually have a pending update, so the other rows do not receive a NULL.

That last point is the trap in this pattern. If you write the subquery in the SET clause but leave the WHERE clause out, every row gets the subquery's result, and for the products with no matching update the subquery returns NULL — so their price becomes NULL. The SET subquery and the WHERE clause have to agree about which rows are in scope.

The UPDATE ... FROM form expresses the same thing more directly. It joins the source table to the target in the update itself, so the WHERE clause can simply filter on the source, and there is no way to accidentally update the wrong rows. It is supported by PostgreSQL, SQLite from 3.33 and SQL Server, and MySQL uses a different syntax entirely — a multi-table UPDATE with a comma and an explicit join condition.

The shape is: a source table with the new values, a target table, and an UPDATE that joins them. The key discipline is that the source rows and the target rows have to line up one to one, or the update becomes ambiguous.

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  department_id INTEGER,
  title VARCHAR(80)
);

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name VARCHAR(60) NOT NULL
);

INSERT INTO staff (id, name, department_id, title) VALUES
  (1, 'Ada Lovelace',   NULL, 'Head of Research'),
  (2, 'Grace Hopper',   NULL, 'Director'),
  (3, 'Alan Turing',    NULL, 'Researcher'),
  (4, 'Katherine Johnson', NULL, 'Mathematician');

INSERT INTO departments (id, name) VALUES
  (1, 'Research'),
  (2, 'Engineering');

UPDATE staff
SET department_id = (
  SELECT d.id
  FROM departments AS d
  WHERE d.name = CASE staff.title
                   WHEN 'Director'        THEN 'Engineering'
                   ELSE 'Research'
                 END
)
WHERE department_id IS NULL;

SELECT s.name, s.title, d.name AS department
FROM staff AS s
LEFT JOIN departments AS d ON d.id = s.department_id
ORDER BY s.id;

Four rows come back. Ada Lovelace, Alan Turing and Katherine Johnson are in Research, and Grace Hopper is in Engineering, because the CASE expression maps each title to a department name and the subquery then looks up the matching id.

The WHERE clause is doing real work here. It restricts the update to rows whose department is still unknown, which makes the statement safe to re-run — a second execution matches nothing, because the rows that were updated no longer satisfy the condition. That is idempotence, and it is a property worth designing into any update you might have to replay.

One thing to watch: the CASE expression references staff.title, which is the outer table's column. That makes the subquery correlated, so it is evaluated once per staff row. With a small staff table that is nothing; with a large one, pre-join the tables in a derived table, do the CASE and the lookup there, and then update from the derived result. The UPDATE ... FROM form does that in one statement, which is why it is the shape to reach for when the source table has many columns or the lookup is not on a unique key.

And remember that the update has to be verified. Run the same SELECT before the UPDATE to see which rows would change, and check the affected-row count afterwards — a statement like this that matches more rows than expected is the kind of mistake that is invisible once it has run.