1

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.

JapanRob
  • 354
  • 3
  • 16
  • You may look for some gems that will wrap what's been said in this [question](https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql) – Marcin Kołodziej Nov 05 '18 at 02:06

1 Answers1

1

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)
Ilya Konyukhov
  • 2,666
  • 1
  • 12
  • 21
  • Suuuuper quick addendum before I accept -> If I try to do this with a timestamp in the value (for example, deleted_at manual setting) it says that `ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR: column "deleted_at" is of type timestamp without time zone but expression is of type text.` Is that something I can fix? – JapanRob Nov 06 '18 at 11:19
  • @JapanRob Right, every value from `VALUES` table in the query like this is considered as of `text` type, that's why it may need to be converted to be inserted into the table. For timestamps you could do this conversion: `SET ..., deleted_at = to_timestamp(uv.deleted_at, 'YYYY-MM-DD HH24:MI:SS OF')`. – Ilya Konyukhov Nov 06 '18 at 15:18