Posted about 4 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 =

Does your version of Ruby on Rails still receive security updates?
Rails LTS provides security patches for old versions of Ruby on Rails (3.2 and 2.3).

Owner of this card:

Arne Hartherz
Last edit:
22 days 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 cookies to improve usability and analyze traffic.
Accept or learn more