Wsrtjtyk

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.

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)