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:

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.

CodePudding user response：

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.