MySQL Update Multiple Fields With DIfferent Values and Conditions For Multiple Rows

Question

I'm seeking the best way to update multiple rows. I have a large record and data which has to be updated daily.

Data sample table_user as t:

user	balance	cost	used
A	100	30	60
B	50	60	120
C	20	10	60

Usually in single query it goes like this:

UPDATE `t` SET `balance` = `balance` - 30, `used` = `used` + 30 WHERE `user` = 'A' AND `balance` - 30 > 0;
UPDATE `t` SET `balance` = `balance` - 60, `used` = `used` + 60 WHERE `user` = 'B' AND `balance` - 60 > 0;
UPDATE `t` SET `balance` = `balance` - 10, `used` = `used` + 10 WHERE `user` = 'C' AND `balance` - 10 > 0;

User A and C records will be updated while B's won't be updated because the balance is not enough.

I've seen examples using CASE WHEN, but usually, it's only to update one field. Is there any better way to update the rows simultaneously without disturbing other transactions that may happen to the database? I use single queries and it take too much time for the loop and execution.

Akina · Accepted Answer · 2022-09-14 05:32:52Z

1

You must provide the data as a rowset and use multiple-table UPDATE:

UPDATE t
JOIN ( SELECT 'A' AS user, 30 AS balance_change 
       UNION ALL
       SELECT 'B', 60
       UNION ALL
       SELECT 'C', 10 ) AS t1 USING (user)
SET t.balance = t.balance - t1.balance_change, 
    t.used = t.used + t1.balance_change 
WHERE t.balance >= t1.balance_change;

Of course, you may provide the data as solid literal, in serialized form (CSV, JSON, etc.), and parse it to rowset in subquery.

answered Sep 14, 2022 at 5:32

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

MySQL Update Multiple Fields With DIfferent Values and Conditions For Multiple Rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related