ActiveRecord - Update single column data of different records in single query

Question

Is it possible to update following data in one single query?

ID             Seq
2               2
1               4
4               1
3               3

It would be best if it could be done with rails active records, else with sql query would be perfectly fine too.

Reason for doing so is I have scenario where I need to update a group of ids frequently based on ajax request. I believe having it done in single query is much more optimized.

I wasn't the downvote, but when I first read the question it needed improvement. — edhedges, Aug 09 '12 at 12:21
No problem. Found the answer for my own question. Will post shortly. — TonyTakeshi, Aug 09 '12 at 12:52

score 1 · Answer 1 · answered Aug 09 '12 at 03:13

1

I guess you're asking whether you can complete set of updates in a single transaction.

In rails, you can use ActiveRecord::Base.transaction for this purpose. For example, for a Model, you can:

Model.transaction do
  Model.update(2, {:seq => 2} )
  Model.update(1, {:seq => 4} )
  Model.update(4, {:seq => 1} )
  Model.update(3, {:seq => 3} )
end

answered Aug 09 '12 at 03:13

shigeya

4,862
3
32
33

Thanks. It definitely one of the solutions. – TonyTakeshi Aug 10 '12 at 03:12

score 0 · Answer 2 · answered Aug 09 '12 at 08:25

We can not directly call multiple updates in a single query using Active Record. As you said we can run it as a sql query in rails.

sql = ActiveRecord::Base.connection();

sql.execute("INSERT into products (id,name,description) VALUES (id-1,'name4','desc4'),(id-2,'name5','desc5'),(id-3,'name6','desc7') ON DUPLICATE KEY UPDATE name=VALUES(name),description=VALUES(description);")

score 0 · Accepted Answer · edited May 23 '17 at 10:34

Found the solution I want from here and here.

This is how I do it with active records.

_params = ["12", "9", "13", "14", "15", "16"]
db = ActiveRecord::Base.connection();

query = "UPDATE #{self.table_name} SET display_order = CASE id "       
_params.each_with_index do |d,i|
  query += sanitize_sql(["WHEN ? THEN ? ",d,i])
end

query += "END "
query += "WHERE id in ('#{_params.join("','")}') "

ActiveRecord - Update single column data of different records in single query

3 Answers3