Performing a SQL UPDATE in Rails 14
I saw this question on the Ruby on Rails mailing list:
...If you had yes/no fields stored as 1’s and 0’s could you write a simple statement to iterate though all of them and change the 1 to a “Yes”?
I know you can do it individually but how about all of them?
This is an excellent question. Indeed, there are many ways to update a single object:
@customer.update_attribute :zip, '60620'
# or
@customer.update_attributes :zip => '60620'
# or
@customer.zip = '60620'
@customer.save
@customer.find(:all).each { |customer| customer.update_attribute :zip, '60623' }
is not advisable, unless you’re sure that the number of customers is relatively small. If you have 100,000 customers, you will loop through each one, generating a lot of traffic to the database – and it will probably take a very long time.
Instead, you can use the update_all class method of ActiveRecord::Base. You can pass two string parameters (the ‘set’ part and the ‘where’ part):
Customer.update_all 'zip = 60620', 'zip IS NULL'
The SQL statement you get will be equivalent to:
UPDATE Customers SET zip = '60620' WHERE (zip is NULL)
The second parameter is optional, so you can leave it off if you want to update every row:
Customer.update_all 'wants_email = 0'
Even better, the first parameter can be a hash instead of a SQL fragment:
Customer.update_all :zip => '60620', :wants_email => 0, 'zip IS NULL'
But beware! Using update_all bypasses all of your model validations, so you need to make sure you know what you’re doing.
Essential Rails: Up to speed with Rails in just two days.
Nice article. It didn't occur to me that I can do this via Active record.
Excellent thanks. I came across this the other day, but it didn't quite do what I wanted to do.
Is there any way to put any logic into the set? So you can do a calculation on the original value, for example to add 5 to every price or something?
You might want to add somewhere that even @customer.update_attribute ( notice the singalr form ) bypasses all the model validations. It's overridden in validations.rb file of activerecord where it calls save without validations.
Thanks, Pratik
@Steve: If you pass string arguments (instead of a hash like I showed near the end), you can supply any valid SQL fragment that will fit into the "SET" and "WHERE" clauses of the UPDATE statement.
@Pratik: Good point, thanks for that.
Nice article. Thanks!
I just recently subscribed to your blog, and this post is hits an issue I've just worked through.
It should be noted that when the first param to update_all method is a hash of update values the SQL syntax generated is...
update table set colx = valx AND coly = valy;
The issue being the "AND" where usual SQL syntax is for this to be a "," (comma). While this "AND" syntax apparently works in MySQL, it does not work in Firebird. Other DB problems may exist, but I only tested those two.
Just a word of caution for those not using MySQL.
Regards
Andy: Another good point, I should have explicitly said that the values get ANDed together. If a comma is better SQL syntax in this case, you should definitely consider submitting a patch to the Rails trac (or if you don't have time, let me know and I'll whip one up). (It worked for me on Postgres, too, by the way).
Is there any way to give custom sql statement that will update it table? I am asking since I have complicated SQL statement that I would like to execute.
Thanks
Brian,
You can use: ModelName.update_all ["new value"], ["condition"]
Thanks
Management.updateall( :accountcode =>@accounttoid, :accountcode => @accountfrom_id )
I'm trying to set the :accountcode in the db to the value in @accountto_id , where db field ":accountcode" equals the value @accountfrom_id. What happens is that all the values change to the value in @accountfromid
I figured it out , here the code if it help anyone
Management.updateall( "accountcode = #{@accounttoid}","accountcode = #{@accountfrom_id}")
thanks for the help…i’m testing it out and will share the results here too
It must not be supposed that our experience would be falsified.
By virtue of my free epoche with respect to the being of the experienced world, the momentous fact is that, by reconciling with experiences, only in reflection do we “direct” ourselves to the Objective world and to its perceptual directedness to separated modes of consciousness.