When you want to UPDATE a table with information from an associated table, you can JOIN the associated table into the statement.

Example

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

• Prevents you from data breaches and liability risks
• Upgrade at your own pace
• Works with modern Rubies

Read more Show archive.org snapshot

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.