Search code examples
sqlpostgresqltypeorm

How to Increment different rows with different values in one query SQL?

This is my user_wallets table:

id balance
A 70
B 40

I want to increment balance of user A with 30 and balance of user B with 10 in a single query.

and I want to know whether it's possible in TypeORM or not. ( raw SQL will do the trick for me as well )

Thank you in advance.

Solution

  • You can use values like this:

    update t
    set t.balance = t.balance + v.inc
    from (values ('A', 30), ('B', 10)) v(user, inc)
    where t.user = v.user;

    Putting all the changes in values() makes the query easy to maintain (adding new users, for instance). It is also much less cumbersome than a big case expression. And the where clause filters down only to the rows that are changing.