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:

Copy
+-----------------+
| 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:

Copy
+-----------------+
| Employee        |                   +------------+
|-----------------| n               1 | Department |
| email           |-------------------|------------+
| department_id   |                   | name       |
| department_name |                   +------------+
+-----------------+

You add the column like this:

Copy
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:

Copy
UPDATE employees
LEFT JOIN departments ON employees.department_id = departments.id
SET department_name = departments.name

In PostgreSQL the syntax is a little unfamiliar (you also might want to look at this example):

Copy
UPDATE employees
SET department_name = departments.name
FROM departments
WHERE employees.department_id = departments.id

Check out our new e-book:

Growing Rails Applications in Practice

Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Learn more