Updating Multiple Records with varying conditions in a single query

user_4723628 0
Viewed 1700

With Typeorm + NestJS + Postgres is there a way to update multiple records with varying conditions and varying values in a single query. Normally I could do

await getConnection()
  .createQueryBuilder()
  .update(Entity)
  .set({ columnName: "newValue" })
  .where({ id: In(1,2,3,4,5,6) })
  .execute();

and this will update all entries with the specified ID. But in the case of having the following data structure

const array = [{id: 1, value: 'New Value For Record 1'},..., {id: 1000, value: 'New Value For Record 1000'}]

I could use for loop to update each single entry as below:

array1.forEach(rec => {
  usersRepo.update(
     { id: rec.id },
     {
        columnName: rec.value
     }
  );
})

but this does not seem to be efficient and won't give good performance. Is there a way to do achieve multiple update on varying conditions with query builder.

query-builder typeorm nestjs postgresql javascript
1 Answers

You can achieve that we two queries

  1. Get all the rows you need, basically a find would be enough, like this.
  2. after that map the results array to do your changes
  3. Last thing to do is to use save method, from Typeorm docs:

save - Saves a given entity or array of entities. If the entity already exists in the database, then it's updated. If the entity does not exist in the database yet, it's inserted.

const array = await yourRepo.find()
const newArr = array.map(el=> ({...el, value: 'New Value For Record'+el.id})
await yourRepo.save(newArr)

Cheers.

user_56719090
Related