Updating several records at once in rails

Question

In a rails 2 app I'm building, I have a need to update a collection of records with specific attributes. I have a named scope to find the collection, but I have to iterate over each record to update the attributes. Instead of making one query to update several thousand records, I'll have to make several thousand queries.

What I've found so far is something like Model.find_by_sql("UPDATE products ...)

This feels really junior, but I've googled and looked around SO and haven't found my answer.

For clarity, what I have is:

ps = Product.last_day_of_freshness
ps.each { |p| p.update_attributes(:stale => true) }

What I want is:

Product.last_day_of_freshness.update_attributes(:stale => true)

Answer 1

It sounds like you are looking for ActiveRecord::Base.update_all - from the documentation:

Updates all records with details given if they match a set of conditions supplied, limits and order can also be supplied. This method constructs a single SQL UPDATE statement and sends it straight to the database. It does not instantiate the involved models and it does not trigger Active Record callbacks or validations.

Product.last_day_of_freshness.update_all(:stale => true)

Actually, since this is rails 2.x (You didn't specify) - the named_scope chaining may not work, you might need to pass the conditions for your named scope as the second parameter to update_all instead of chaining it onto the end of the Product scope.

Answer 2

Have you tried using update_all ?

http://api.rubyonrails.org/classes/ActiveRecord/Relation.html#method-i-update_all

Answer 3

For those who will need to update big amount of records, one million or even more, there is a good way to update records by batches.

product_ids = Product.last_day_of_freshness.pluck(:id)
iterations_size = product_ids.count / 5000

puts "Products to update #{product_ids.count}"

product_ids.each_slice(5000).with_index do |batch_ids, i|
  puts "step #{i} of iterations_size"
  Product.where(id: batch_ids).update_all(stale: true)
end

If your table has a lot indexes, it also will increase time for such operations, because it will need to rebuild them. When I called update_all for all records in table, there were about two million records and twelve indexes, operation didn't accomplish in more than one hour. With this approach it took about 20 minutes in development env and about 4 minutes in production, of course it depends on application settings and server hardware. You can put it in rake task or some background worker.

Answer 4

Loos like update_all is the best option... though I'll maintain my hacky version in case you're curious:

You can use just plain-ole SQL to do what you want thus:

ps = Product.last_day_of_freshness
ps_ids = ps.map(%:id).join(',') # local var just for readability
Product.connection.execute("UPDATE `products` SET `stale` = TRUE WHERE id in (#{ps_ids)")

Note that this is db-dependent - you may need to adjust quoting style to suit.