Skip to content

Instantly share code, notes, and snippets.

@zoltan-nz
Last active October 28, 2022 03:54
Show Gist options
  • Save zoltan-nz/6390986 to your computer and use it in GitHub Desktop.
Save zoltan-nz/6390986 to your computer and use it in GitHub Desktop.
UPDATE more record with one query using CASE sql statement in Ruby on Rails. In this example update sort_order integer based on the sort order of products.
#List of product ids in sorted order. Get from jqueryui sortable plugin.
#product_ids = [3,1,2,4,7,6,5]
#product_ids.each_with_index do |id, index|
# Product.where(id: id).update_all(sort_order: index+1)
#end
##CASE syntax example:
##Product.where(id: product_ids).update_all("sort_order = CASE id WHEN 539 THEN 1 WHEN 540 THEN 2 WHEN 542 THEN 3 END")
case_string = "sort_order = CASE id "
product_ids.each_with_index do |id, index|
case_string += "WHEN #{id} THEN #{index+1} "
end
case_string += "END"
Product.where(id: product_ids).update_all(case_string)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment