Update multiple records in one query in ruby on rails

Question

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

What will you be "updating"? The name? The age? Maybe even the id?? Or did you mean INSERT 3 new rows instead of UPDATE 3 existing rows? Or, maybe, "upsert" wherein you either add new row(s) or modify existing row(s) based on some unique key (presumably the id). — Rick James
– Rick James, Commented Oct 6, 2021 at 20:22
I have updated my question. I like to find the records by id and update name and age. — kernification
– kernification, Commented Oct 7, 2021 at 7:07

bugged · Accepted Answer · 2021-10-06 17:16:27Z

1

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

answered Oct 6, 2021 at 17:16

bugged

631 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

Shouldn't that link say matched instead of violated where it says "... which allows you to specify fields whose values should be updated if a primary or unique key constraint is violated"??

Rick James · Accepted Answer · 2021-10-07 18:05:57Z

0

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.)

edited Oct 7, 2021 at 18:05

answered Oct 7, 2021 at 18:00

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Update multiple records in one query in ruby on rails

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related