When you want to UPDATE a table with information from an associated table, you can JOIN the associated table into the statement.
Example
Let's say you have a database schema where an Employee belongs_to :department:
+-----------------+
| Employee | +------------+
|-----------------| n 1 | Department |
| email |-------------------|------------+
| department_id | | name |
+-----------------+ +------------+
Because of performance reason you decide to cache the department's name into a new column Employee#department_name:
+-----------------+
| Employee | +------------+
|-----------------| n 1 | Department |
| email |-------------------|------------+
| department_id | | name |
| department_name | +------------+
+-----------------+
You add the column like this:
ALTER TABLE employees ADD COLUMN department_name TEXT;
Now you need to backfill existing Employee records with the new department_name. Since the department's name lives in another table, you need to JOIN both tables during the UPDATE.
In MySQL you can do it like this:
UPDATE employees
LEFT JOIN departments ON employees.department_id = departments.id
SET department_name = departments.name
In PostgreSQL the syntax is a little unfamiliar:
UPDATE employees
SET department_name = departments.name
FROM departments
WHERE employees.department_id = departments.id
There is also a card on updating multiple columns with multiple joins in PostgreSQL.
Posted by Arne Hartherz to makandra dev (2015-04-24 09:50)