Menu
How can I use update_all, if I want to update a column of 300,000 records all with a variety of different values?
What I want to do is something like:
Model.update_all(:column => [2,33,94,32]).where(:id => [22974,22975,22976,22977])
But unfortunately this doesn't work, and it's even worse for 300,000 entries.
865
just make a transaction statement, with multiple update statement and commit.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
To do a conditional update depending on whether the current value of a column matches the condition, you can add a WHERE clause which specifies this. The database will first find rows which match the WHERE clause and then only perform updates on those rows.
From the ActiveRecord#update documentation:
people = { 1 => { "first_name" => "David" }, 2 => { "first_name" => "Jeremy" } }
Person.update(people.keys, people.values)
So in your case:
updates = {22974 => {column: 2}, 22975 => {column: 33}, 22976 => {column: 94}, 22977 => {column: 32}}
Model.update(updates.keys, updates.values)
Edit: Just had a look at the source, and this is generating n SQL queries too... So probably not the best solution
187
The only way I found to do it is to generate INSERT INTO request with updated values. I'm using gem "activerecord-import" for that.
For example, I have a table with val values
+--------+--------------+---------+------------+-----+-------------------------+-------------------------+
| pkey | id | site_id | feature_id | val | created_at | updated_at |
+--------+--------------+---------+------------+-----+-------------------------+-------------------------+
| 1 | | 125 | 7 | 88 | 2016-01-27 10:25:45 UTC | 2016-02-05 11:18:14 UTC |
| 111765 | 0001-0000024 | 125 | 7 | 86 | 2016-01-27 11:33:22 UTC | 2016-02-05 11:18:14 UTC |
| 111766 | 0001-0000062 | 125 | 7 | 15 | 2016-01-27 11:33:22 UTC | 2016-02-05 11:18:14 UTC |
| 111767 | 0001-0000079 | 125 | 7 | 19 | 2016-01-27 11:33:22 UTC | 2016-02-05 11:18:14 UTC |
| 111768 | 0001-0000086 | 125 | 7 | 33 | 2016-01-27 11:33:22 UTC | 2016-02-05 11:18:14 UTC |
+--------+--------------+---------+------------+-----+-------------------------+-------------------------+
products = CustomProduct.limit(5)
products.each_with_index{|p, i| p.val = i}
CustomProduct.import products.to_a, :on_duplicate_key_update => [:val]
All you records will be updated in single request. Please find out gem "activerecord-import" documentation for more details.
+--------+--------------+---------+------------+-----+-------------------------+-------------------------+
| pkey | id | site_id | feature_id | val | created_at | updated_at |
+--------+--------------+---------+------------+-----+-------------------------+-------------------------+
| 1 | | 125 | 7 | 0 | 2016-01-27 10:25:45 UTC | 2016-02-05 11:19:49 UTC |
| 111765 | 0001-0000024 | 125 | 7 | 1 | 2016-01-27 11:33:22 UTC | 2016-02-05 11:19:49 UTC |
| 111766 | 0001-0000062 | 125 | 7 | 2 | 2016-01-27 11:33:22 UTC | 2016-02-05 11:19:49 UTC |
| 111767 | 0001-0000079 | 125 | 7 | 3 | 2016-01-27 11:33:22 UTC | 2016-02-05 11:19:49 UTC |
| 111768 | 0001-0000086 | 125 | 7 | 4 | 2016-01-27 11:33:22 UTC | 2016-02-05 11:19:49 UTC |
+--------+--------------+---------+------------+-----+-------------------------+-------------------------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
