Posted over 6 years ago. Visible to the public.

PostgreSQL vs MySQL: How to UPDATE using a JOIN

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


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 = SET department_name =

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

UPDATE employees SET department_name = FROM departments WHERE employees.department_id =

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Arne Hartherz
Last edit:
about 2 years ago
by Besprechungs-PC
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more