Lucian Ghinda
Random Thoughts about Building Software

Random Thoughts about Building Software

How to update multiple different values at the same time in one query

How to update multiple different values at the same time in one query

How to create a raw SQL to update in Rails different values for each record in one single query

Lucian Ghinda's photo
Lucian Ghinda
·Apr 1, 2022·

3 min read

Subscribe to my newsletter and never miss my upcoming articles

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
 
Share this