Context

You want to update the same column for multiple records in one query and set different values for each record.

Example:

class Car < ActiveRecord::Base
# has attributes: id, license_plate
end

Let's say you want to update license plates and you have a list of car ids and associated license plates:

updates = {
  1 => "EU-DE-123245"
  2 => "EU-NL-12456"
  3 => "EU-FR-12455"
  4 => "EU-RO-87613"
}

Problem

You can do this in multiple queries. Here is an example:

updates.each do |car_id, license_place|
  Car.find(car_id).update_column(:license_place, license_place)
end

The problem with this solution is that it will do multiple queries and sometimes this could come with a performance penalty.

So you want to find a way to update all records with different values in one single query.

Solution

# @param column [String] The name of the column to be updated with new values
# @param updates [Array<Hash>] The hash key is the id of the record and hash value is the new value to be set
# @example Update amount for records with id 1 and 2 
#     update_column_for_all_records("amount", [ {
#       1 => "EU-DE-123245"
#       2 => "EU-NL-12456"
#       3 => "EU-FR-12455"
#       4 => "EU-RO-87613"
#     }])

def update_column_for_all_records(column, updates)
  cases = []
  ids = updates.keys.map(&:to_i)

  Car.where(id: ids).each do 
    id = _1.id
    new_value = updates[id]
    cases << sanitize_case(id, new_value) 
  end

  update_query = "#{column} = CASE id #{cases.join(" ")} END"

  Car.where(id: ids).update_all(update_query)
end

def sanitize_case(id, new_value)
  Car.sanitize_sql [ "WHEN :id THEN :value", { id: id, value: new_value} ]
end

Explanation of the solution

We are using a kind of raw SQL to update all records in one query.

What we want to execute in SQL is something like this:

UPDATE
    "cars"
SET
    "cars"."license_plate" = CASE "cars"."id"
    WHEN 1 THEN
        'EU-DE-123245'
    WHEN 2 THEN
        'EU-NL-12456'
    END
WHERE
    "cars"."id" IN(1, 2)

Things to consider

If you use this in #Rails please take into consideration:

update_all will not execute callbacks nor validations

As specified in the Rails docs, update_all:

It does not instantiate the involved models and it does not trigger Active Record callbacks or validations.

Thus it will also not update the updated_at column.

You might need to cast your values in the WHEN THEN query

For example, if you will need to update a column that has the format timestamp then you might need to change the method to support casting and add to the cases array something like:

cases << "WHEN #{id} THEN CAST('#{new_value.to_fs(:db)}' as timestamp)"

Notice in this line the expectation is that new_value is a Rails DateTime object and I am using to_fs(:db) (see more here) to make sure I am converting the proper value to timestamp.

Here is a more full example with casting:

# @param column [String] The name of the column to be updated with new values
# @param updates [Array<Hash>] The hash key is the id of the record and hash value is the new value to be set
# @example Update amount for records with id 1 and 2 
#     update_column_for_all_records("amount", [{ id: 1, value: "EU-DE-123245", type: "string" }, {id: 2, value: "EU-NL-12456", type: "text"}])

def update_column_for_all_records(column, updates)
  cases = []
  ids = updates.map { _1[:id] }

  Car.where(id: ids).each do 
    id = _1[:id]
    new_value = _1[:value] 
    cases << sanitize_case(id, new_value, _1[:type]) 
  end

  update_query = "#{column} = CASE id #{cases.join(" ")} END"

  Car.where(id: ids).update_all(update_query)
end

def sanitize_case(id, new_value, type)
  native_type = adapter_type(type)
  Car.sanitize_sql [ "WHEN :id THEN CAST(:value as #{native_type})", { id: id, value: new_value} ]
end

# This works for PostgreSQL for example
def adapter_type(type)
  ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES.dig(type.to_s, :name) || "text"
end