method

update_all

rails latest stable - Class: ActiveRecord::Base

update_all(updates, conditions = nil, options = {})

public

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.

Parameters

updates - A string of column and value pairs that will be set on any records that match conditions. This creates the SET clause of the generated SQL.
conditions - An SQL fragment like "administrator = 1" or [ "user_name = ?", username ]. See conditions in the intro for more info.
options - Additional options are :limit and :order, see the examples for usage.

Examples

  # Update all billing objects with the 3 different attributes given
  Billing.update_all( "category = 'authorized', approved = 1, author = 'David'" )

  # Update records that match our conditions
  Billing.update_all( "author = 'David'", "title LIKE '%Rails%'" )

  # Update records that match our conditions but limit it to 5 ordered by date
  Billing.update_all( "author = 'David'", "title LIKE '%Rails%'",
                        :order => 'created_at', :limit => 5 )

# File activerecord/lib/active_record/base.rb, line 830
      def update_all(updates, conditions = nil, options = {})
        sql  = "UPDATE #{quoted_table_name} SET #{sanitize_sql_for_assignment(updates)} "

        scope = scope(:find)

        select_sql = ""
        add_conditions!(select_sql, conditions, scope)

        if options.has_key?(:limit) || (scope && scope[:limit])
          # Only take order from scope if limit is also provided by scope, this
          # is useful for updating a has_many association with a limit.
          add_order!(select_sql, options[:order], scope)

          add_limit!(select_sql, options, scope)
          sql.concat(connection.limited_update_conditions(select_sql, quoted_table_name, connection.quote_column_name(primary_key)))
        else
          add_order!(select_sql, options[:order], nil)
          sql.concat(select_sql)
        end

        connection.update(sql, "#{name} Update")
      end

7Notes

Use hash form of updates argument

wsmith67 · Nov 12, 20098 thanks

The examples are unfortunate, because passing a string as the updates argument is an invitation to SQL injection attacks. Don't do this!

Billing.update_all("author='#{author}'")

Use the hash form of updates instead:

Billing.update_all(:author => author)

Then the SQL adapter will quote everything safely. Even if [you think] you're sure there's no quoting issue, it's better to cultivate the habit of using the hash form just in case you missed something.

Same with conditions--use the hash or array form rather than a string if there are variables involved.

BTW, to do this and give options, of course you'll need to put the braces back in:

Billing.update_all({:author => author},
                 ['title like ?', "#{prefix}%"])

update_all and serialized attributes

bquorning · Aug 16, 2010

If you use update_all to change an attribute marked as serialized ( using ActiveRecord::Base.serialize ), you need to call to_yaml yourself:

User.update_all({ :preferences => { :first_name => 'John', :last_name => 'Doe' }.to_yaml })

update_all (and delete_all) don't play nicely with default_scope

wiseleyb · Feb 4, 2011

If you have

class Topic < ActiveRecord::Base
default_scope :conditions => "forums.preferences > 1", :include => [:forum]
end

and you do a

Topic.update_all(...)

it’ll fail with

Mysql::Error: Unknown column 'forums.preferences' in 'where clause'

The work around for this is:

Topic.send(:with_exclusive_scope) { Topic.update_all(...) }

You can monkey patch this using this code (and requiring it in environment.rb or else where)

module ActiveRecordMixins
class ActiveRecord::Base
  def self.update_all!(*args)
    self.send(:with_exclusive_scope) { self.update_all(*args) }
  end
  def self.delete_all!(*args)
    self.send(:with_exclusive_scope) { self.delete_all(*args) }
  end
end
end
end

Then just you update_all! or delete_all! when it has a default scope.

If on Rails 3

Vidmantas · Feb 7, 2011

If you're on Rails 3, you should look into http://apidock.com/rails/ActiveRecord/Relation/update_all

Clarification with use of update_all

newdark · Oct 2, 2012

I would like to point out that if you are on rails 2.3.11 or lower you will not be able to run ledermann code.

==== Ledermann Code user.messages.update_all(:read => true)

If you are running 2.3 or later it you will have to use James code

==== James Code

Message.update_all({:read => true}, {:id => user.messages})

thanks guys for all the code help

Usage with enum

GuskiS · Jul 2, 2015

With enum fields you must use integer values: ==== code Model.update_all(type: Model.types[specific_type])

Skip validation

radin · Aug 2, 2015

update_all : skip validations, and will save the object to the database regardless of its validity. They should be used with caution.