Skip to content

Instantly share code, notes, and snippets.

@samholst
Forked from zoltan-nz/rails_sql_case_example.rb
Created January 10, 2021 00:00
Show Gist options
  • Save samholst/53030f2181f177155336344f2934cd4b to your computer and use it in GitHub Desktop.
Save samholst/53030f2181f177155336344f2934cd4b 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