Update multiple rows at one time

[hsk-kr]

trafficstars

Feature Description

The Problem

There seems no way to update many rows by one call.

The Solution

Implement a method that to update multiple rows by one call.

For example)

You wanna make a query like below.

UPDATE TOKENS
    SET word = (case when id = 1 then 'A'
                         when id = 2 then 'B'
                         when id = 3 then 'C'
                    end)
    WHERE id in (1, 2, 3);

And I think It's not bad to provide the method like this.

const data = [
  {
    id: 1,
    word: 'A'
  },
  {
    id: 2,
    word: 'B'
  },
  {
    id: 3,
    word: 'C'
  }
];

...updateBulkMethod(data);

It'll must be considered by many aspects.

Considered Alternatives

Implement a method yourself to update multiple rows at one time .

Additional Context

Relevant Database Driver(s)

• [ ] aurora-data-api
• [ ] aurora-data-api-pg
• [ ] better-sqlite3
• [ ] cockroachdb
• [ ] cordova
• [ ] expo
• [ ] mongodb
• [x] mysql
• [ ] nativescript
• [ ] oracle
• [ ] postgres
• [ ] react-native
• [ ] sap
• [ ] sqlite
• [ ] sqlite-abstract
• [ ] sqljs
• [ ] sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• [ ] Yes, I have the time, and I know how to start.
• [ ] Yes, I have the time, but I don't know how to start. I would need guidance.
• [ ] No, I don't have the time, although I believe I could do it if I had the time...
• [x] No, I don't have the time and I wouldn't even know how to start.

[eporomaa]

We would like to see this feature, specifically for postgres. I would be happy to implement it if given some guidance.

[arunraj6]

@ePoromaa Any updates on this feature?

[eporomaa]

@arunraj6 Not from my side. For our usecase we created stored functions in postgres and call them manually:

await entityManager.query(`SELECT public.f_transact(array[${str}]::public.type_transaction[])`)

public.f_transact is our stored function, we manually validate data prior to creating the string "str".

For future reference: Updating multple rows at once in postgres can be done using psql like such:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

From: https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql

[arunraj6]

@arunraj6 Not from my side. For our usecase we created stored functions in postgres and call them manually:

await entityManager.query(`SELECT public.f_transact(array[${str}]::public.type_transaction[])`)

public.f_transact is our stored function, we manually validate data prior to creating the string "str".

For future reference: Updating multple rows at once in postgres can be done using psql like such:

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

From: https://stackoverflow.com/questions/18797608/update-multiple-rows-in-same-query-using-postgresql

Thanks for these helpful details 👍

[Hexmm]

It's ridiculous to find a solution to the same problem for me. Finally, just use Typeform.Repository.save(Entity[]) is perfect to solve.

Saves all given entities in the database. If entities do not exist in the database then inserts, otherwise updates.

[kyle-seongwoo-jun]

Finally, just use Typeform.Repository.save(Entity[]) is perfect to solve.

I want to use this solution, but I have to use sql function on query, so I couldn't use it.

[zangguojun]

It's ridiculous to find a solution to the same problem for me. Finally, just use Typeform.Repository.save(Entity[]) is perfect to solve.

Saves all given entities in the database. If entities do not exist in the database then inserts, otherwise updates.

But I do not hope that repository.save can insert a record when id is not in database.

[zangguojun]

It's ridiculous to find a solution to the same problem for me. Finally, just use Typeform.Repository.save(Entity[]) is perfect to solve.

Saves all given entities in the database. If entities do not exist in the database then inserts, otherwise updates.

But I do not hope that repository.save can insert a record when id is not in database.

[dileepainivossl]

any update on this, ANY WORKAROUND METHOAD FOR THIS

[alexey-sh]

@dileepainivossl WORKAROUND is plain sql request

[yangli-io]

.save is doing too much magic and can cause some weird bugs in your app. I would highly recommend against if you're serious about your app

.save will try to do a select first and then decide whether or not to save or update. This may seem like it's good but there are many implications. Such as performance and concurrency and behaviour expectations.

[pvHung-nopro]

.save is performing updates one record at a time, meaning if there are 1,000 records, .save will execute 1,000 queries. This is not ideal for application performance.