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:
CopyALTER 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:
CopyUPDATE 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):
CopyUPDATE 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).