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

By refactoring problematic code and creating automated tests, makandra can vastly improve the maintainability of your Rails application.

Owner of this card:

Arne Hartherz
Last edit:
about 1 year 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