Home > database >  Update multiple records in one query in ruby on rails
Update multiple records in one query in ruby on rails

Time:10-08

lets say I have a Array of Hashes like this

users_ar = [
 {
   id: 1,
   name: 'Luke',
   age: 19
 },
 {
   id: 2,
   name: 'Vader',
   age: 44
 },
 {
   id: 3,
   name: 'Yoda',
   age: 129
 }
]

The id is their id in the User model.

How can i update all records at once in ruby on rails (if i don't need to validate the records) for performance reasons if i have thousands of records to update?

I'd like to find existing records by id and update name and age. Im looking for something like this:

users_to_update.update_all(users_ar)

My rails version is 5.2.3 and I'm using MySQL.

Thanks, Andreas

CodePudding user response:

The activerecord-import gem is probably what you need if you're looking to do large batch updates with good performance: https://github.com/zdennis/activerecord-import

CodePudding user response:

In MySQL:

INSERT INTO tbl
    (id, name, age)
      VALUES
    (1, 'Luke', 19),
    (2, 'Vader', 44),
    (3, 'Yoda', 129)
    ON DUPLICATE KEY UPDATE
        name = VALUES(name),
        age = VALUES(age);
    

Although this looks like an "upsert", it is the fastest way I know of to just Update several rows at once. In your case, all the rows already exist (according to the unique id), so it proceeds to Update each of the rows.

(No, I don't know how to say this in RoR. The link provided by bugged may answer this.)

  • Related