CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
JapanRob
JapanRob

Reputation: 384

Updating Multiple Rows in DB in Rails, each with Unique values

I have extensively researched this, and I can't seem to find the answer I need.

I am familiar with Rails transactions, but a transaction in this case would execute several queries and I would rather not do that.

In a single query, how can I update the same column on multiple rows with unique values?

Ex:

update_hash =  {1: 'Bandits on the High Road', 2: 'Broccoli: The Menace'}
Books.where(<id_is_in_update_hash_keys>).each do |b|
  matching_hash_key = b.id
  new_title = update_hash[:matching_hash_key].value
  # problem here because each update is a query
  b.update(title: new_title)
end

Of course, I could wrap it in a transaction, but 10k books still call 10k queries. I use Postgresql, but I don't know the correct, idiomatic way to update that field for multiple objects in a single query. The data has been pre-vetted so there will never be a need to run validations.

If anyone knows either the Rails code to execute, or more likely the Postgresql query that I need to generate, I would be very grateful.

Upvotes: 1

Views: 684

Answers (1)

Ilya Konyukhov
Ilya Konyukhov

Reputation: 2791

With PostgreSQL it's possible with a query like this one:

update_hash = { 1: 'Bandits on the High Road', 2: 'Broccoli: The Menace' }
values = update_hash.map { |k, v| "(#{k}, #{ActiveRecord::Base.connection.quote(v)})" }.join(', ')

query = "
    UPDATE books T
    SET title = uv.new_title
    FROM (VALUES #{values}) AS uv (id, new_title)
    WHERE T.id = uv.id::int"

ActiveRecord::Base.connection.execute(query)

Upvotes: 1

Related Questions