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

Example

Opscomplete powered by

Save money by migrating from AWS to our fully managed hosting in Germany.

• Trusted by over 100 customers
• Ready to use with Ruby, Node.js, PHP
• Proactive management by operations experts

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.