update_all
- 1.0.0 (0)
- 1.1.6 (0)
- 1.2.6 (0)
- 2.0.3 (21)
- 2.1.0 (0)
- 2.2.1 (7)
- 2.3.8 (0)
- 3.0.0
- 3.0.9
- 3.1.0
- 3.2.1
- 3.2.8
- 3.2.13
- 4.0.2
- 4.1.8
- 4.2.1
- 4.2.7
- 4.2.9
- 5.0.0.1
- 5.1.7
- 5.2.3
- 6.0.0
- 6.1.3.1
- 6.1.7.7
- 7.0.0
- 7.1.3.2
- 7.1.3.4
- What's this?
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 )
Scoped using - more simple way
Regarding to the example from james, there is a more simple way to do this:
user.messages.update_all(:read => true)
perform update_all scoped within a has_many collection
For example: having two models, User and Message (user has_many messages, each message has a boolean flag called ‘read’). You want to mark all messages as read for a particular user.
Mark all messages as read for a particular user
Message.update_all({:read => true}, {:id => user.messages})
Use hash form of updates argument
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}%"])
If on Rails 3
If you’re on Rails 3, you should look into
http://apidock.com/rails/ActiveRecord/Relation/update_all
update_all and serialized attributes
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
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.
Usage with enum
With enum fields you must use integer values:
code
Model.update_all(type: Model.types[specific_type])
Skip validation
update_all : skip validations, and will save the object to the database regardless of its validity. They should be used with caution.
Clarification with use of update_all
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