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

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

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

Owner of this card:

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