Posted almost 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.

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

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:

Avatar
Arne Hartherz
Last edit:
almost 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