Rails 8.1 introduces support for joins in update_all for Postgresql and SQLite.

ActiveRecord joins is used to combine records from multiple tables based on associations, generating an SQL INNER JOIN.

With update_all, we can update multiple records quickly without triggering model validations, callbacks, or modifying timestamps.

Combining these two methods is useful when we need to perform bulk updates based on data from associated tables. For example, syncing a column value from a parent record to all its children in a single query.

Before

When we use update_all together with joins, Rails generates a subquery that prevents referencing joined tables in the SET clause, which results in an ActiveRecord::StatementInvalid error.

For example:

class Client < ApplicationRecord
  has_many :projects
end

class Project < ApplicationRecord
  belongs_to :client
end

With Postgresql

Project.joins(:client).update_all("name = clients.name")

Project Update All (14.4ms)  UPDATE "projects" SET name = clients.name WHERE "projects"."id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id")

`exec_params': PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "clients" (ActiveRecord::StatementInvalid)

With SQLite

Project.joins(:client).update_all("name = clients.name")

Project Update All (2.4ms)  UPDATE "projects" SET name = clients.name WHERE ("projects"."id") IN (SELECT "projects"."id" FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id")

SQLite3::SQLException: no such column: clients.name: (ActiveRecord::StatementInvalid)

After

Starting from Rails 8.1, joins in update_all is now supported for Postgresql and SQLite. This feature was already supported by the MySQL adapter.

With MySQL

Project.joins(:client).update_all("name = clients.name")

Project Update All (0.8ms)  UPDATE `projects` INNER JOIN `clients` ON `clients`.`id` = `projects`.`client_id` SET `projects`.`name` = clients.name

=> 1

With Postgresql

Project.joins(:client).update_all("name = clients.name")

Project Update All (0.5ms)  UPDATE "projects" AS "__active_record_update_alias" SET name = clients.name FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id" WHERE "projects"."id" = "__active_record_update_alias"."id"

=> 1

With SQLite

Project.joins(:client).update_all("name = clients.name")

Project Update All (0.3ms)  UPDATE "projects" AS "__active_record_update_alias" SET name = clients.name FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id" WHERE "projects"."id" = "__active_record_update_alias"."id"

=> 1

Note

This is supported as long as the relation doesn’t use a LIMIT, ORDER or GROUP BY clause.

Project.joins(:client).limit(1).update_all("name = clients.name")

Project Update All (3.9ms)  UPDATE "projects" SET name = clients.name WHERE ("projects"."id") IN (SELECT "projects"."id" FROM "projects" INNER JOIN "clients" ON "clients"."id" = "projects"."client_id" LIMIT 1)

SQLite3::SQLException: no such column: clients.name: (ActiveRecord::StatementInvalid)

References

Rails 8.1 introduces support for joins in update_all for Postgresql and SQLite.

Before

After

Rails 7.1 allows passing validate(_check)_constraint through change_table

Rails 8.1 now sorts table columns alphabetically when dumping the schema

Upgrading from Rails 5.2 to Rails 6 - Modern Rails Features