How to update thousands of records

Question

I have to update an age column based on the value in a date of birth column. There are thousands of records to update.

How do I do this using rails?

Is this the right way to do it?

User.update_all(:age => some_method);

def some_method
  age = Date.today.year - dob.year
end

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

Yes, update_all is the right method but no, you can't do it like this. Your some_method will only get called once to set up a database call (I assume you're persisting to a database). You'll then get an error because dob won't be recognised in the scope of the User class.

You'll need to translate your date logic to SQL functions.

Something like (for mysql):

User.update_all("age = year(now()) - 
                year(dob) - 
                (DATE_FORMAT(now(), '%m%d') < DATE_FORMAT(dob, '%m%d'))")

(NB. the date_format stuff is so that you get the right age for people who's birthdays are later in the year than the current date - see this question for more details)

edited May 23 '17 at 12:28

Community

1
1

answered Mar 24 '14 at 12:13

Shadwell

34,314
14
94
99

its is not if there are callbacks to trigger – apneadiving Mar 24 '14 at 12:16
1

Good point. If there's other logic hanging off this then you'll need to iterate through the users. Make sure you do it in batches though. – Shadwell Mar 24 '14 at 12:18

score 0 · Answer 2 · answered Mar 24 '14 at 12:29

The other option is to use one of the batches functionality in rails.

User.where(some_condition).find_in_batches do |group_of_users|
 # some logic
 # e.g. group_of_users.update_all(:age => some_logic)
end

This would lock your db for less time. Note that you should pretty much always update with a condition in mind. I can't think of many cases you would want to update an entire table every time something happens.

There are a few options checkout the rails docs or the api.

score 0 · Answer 3 · answered Mar 24 '14 at 14:12

your query is right.

There are many way to update record in a batch/lot.

But, I think that your query is best. Because it is rails query that will support every condition for all database.

for updating more than one attributes

Model.update_all(:column1 => value1, :column2 => value2, ........)

or

you can use :

Model.update_all("column1 = value1, column2 = value2, ........")

How to update thousands of records

3 Answers3

Linked