1

Is it possible to update multiple rows with one query? Like in insert i can pass an array of objects and each key refers each column. Is there anything like that for an update query?

I have an array of objects (id, value) and i want to update all the fields that match id from the object with the value from the same object.

Jan Kowalski
  • 263
  • 2
  • 5
  • 13

1 Answers1

2

There is a way to update it using PostgreSQL query and it was answered here. But it requires some magic and ugly .raw code to use this with knex. So, I'd recommend using multiple update statements in one transaction. And synchronize them using Promise.all([updates]).

coockoo
  • 2,294
  • 17
  • 26
  • if you use a `Promise.all` with a bunch of sql queries (all belonging to the same transaction), is knex smart enough/is thre the option to combine all the sql statements together and send it to the db in one go? – friartuck Jul 29 '22 at 12:38
  • otherwise if you do an update of, say 10000 rows it feels like the multiple update statements in one transaction approach would have significantly worse performance – friartuck Jul 29 '22 at 12:39
  • @friartuck yeah but you need to store all the update queries in an array (do not await first). You need to have an array of promises then you `Promise.all(arrayOfPromises)` – Ryan Garde Aug 12 '22 at 08:19