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

Question

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.

You may look for some gems that will wrap what's been said in this question — Marcin Kołodziej
– Marcin Kołodziej, Commented Nov 5, 2018 at 2:06

Ilya Konyukhov · Accepted Answer · 2018-11-05 03:47:53Z

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)

answered Nov 5, 2018 at 3:47

Ilya Konyukhov

2,7911 gold badge14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JapanRob Over a year ago

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?

Ilya Konyukhov Over a year ago

@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').

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related