complex_update_all.rb

# Example:
# # Fish has a has_been_caught_by_someone_called(...) scope that is too complex (several joins, and group bys)
# Fish.count # => 999,999,999
# my_fish = Fish.has_been_caught_by_someone_called("bubba")
#
# my_fish.count # => 999,999
# my_fish.update_all(:color => "green") # Too complex, too many joins, group by etc... mysql will not play ball.
# my_fish.all... # => OUT OF MEMORY!!!!!!
# my_fish.each_in_batches{|f| f.update_attribute(:color => "green") } # => Takes more than a decade.
# my_fish.complex_update_all(:color => "green") # => 999,999
#
# # Win!

ActiveRecord::Base.class_eval do
  def self.complex_update_all(updates)
    quoted_primary_key = connection.quote_column_name(primary_key)
    id_finder_sql = construct_finder_sql(:select => "#{quoted_table_name}.#{quoted_primary_key}")
    temp_table = "ids_ready_for_mass_assignment"
    begin
      connection.execute("CREATE TEMPORARY TABLE #{temp_table} #{id_finder_sql}")
      sql = "UPDATE #{quoted_table_name} INNER JOIN #{temp_table} ON #{quoted_table_name}.#{quoted_primary_key} = #{temp_table}.#{quoted_primary_key} SET #{sanitize_sql_for_assignment(updates)}"
      connection.update(sql, "#{name} Complex Update")
    ensure
      connection.execute("DROP TEMPORARY TABLE #{temp_table}") rescue nil
    end
  end
end