MySQL - Deduct from two columns with a remainder

Question

I have the following table:

Id,  balance, otherBalance
=============================
1,     100,      50

I would like to create an update query that receives a parameter 'amount' and does the following: deduct from both balances columns the entire amount. Starting with the 'balance' column first, and only when it is depleted, deduct the remainder from the 'otherBalance'.

Examples

Executing update(98), will result in:

Id,  balance, otherBalance
=============================
1,     2,      50

Executing update(102), will result in:

Id,  balance, otherBalance
=============================
1,     0,      48

Executing update(151), will result in no change since both balances are less than the amount to be deducted.

Answer 1

You can use the variables to do the trick:

UPDATE table   AS w
  JOIN amounts AS z ON z.w_Id = w.ID
   SET w.balance = IF( (@reminder := w.balance - z.amount) >= 0, @reminder, 0 )
     , w.otherBalance = IF( @reminder >= 0, w.otherBalance, w.otherBalance + @reminder )
;

Here I suppose that amount is fetched from the another table amounts and perform the mass update for all the rows at once.

If the amount is bigger than balance then @reminder become negative therefore I've add it to the otherBalance instead of substraction.

Answer 2

I had a go at this - thanks to a MySQL "feature" (albeit documented - see comments), it was tricky!

I was trying to solve this using pure SQL - it's relatively easy to do it in procedural code. But it can be done using SQL IF you have the update amount parameter in another table!

First I did this:

CREATE TABLE my_tab (my_id INTEGER, balance_1 UNSIGNED, balance_2 UNSIGNED);  
                           -- the reason for `UNSIGNED` is dealt with below!
INSERT INTO my_tab VALUES (1, 100, 50);

CREATE TABLE my_update_tab (update_amount INTEGER);
INSERT INTO my_update_tab VALUES (98);

And then I wrote the following SQL: (check out the dbfiddle.com here)

UPDATE my_tab
SET 
  balance_2 =
    CASE
      WHEN (SELECT update_amount FROM my_update_tab) < balance_1 THEN balance_2 
      WHEN (SELECT update_amount FROM my_update_tab) > balance_1 THEN (balance_2 + balance_1 - (SELECT update_amount FROM my_update_tab))
  END,
  balance_1 = 
    CASE
      WHEN (SELECT update_amount FROM my_update_tab) < balance_1 THEN balance_1 - (SELECT update_amount FROM my_update_tab)
      WHEN (SELECT update_amount FROM my_update_tab) > balance_1 THEN 0
  END;

And then I ran:

SELECT * FROM my_tab;

To get the following (correct but see comments) result:

my_id   balance_1   balance_2
    1           2          50

Please see the comments to this post - there is a problem with MySQL (IMHO, still a bug). Thanks to ypercube for helping me complete this answer!

The MySQL Server doesn't appear to be aware that an UPDATE on one table even to 2 (or many) fields is normally a single transaction which is why balance_2 is updated before balance_1 - totally contrary to any sort of logic - this cost me a whole bunch of time!

PostgreSQL (again, surprise surprise) gets it right - see the dbfiddle here! (1, 2, 50) And this one (1, 0, 10) - where amount is bigger than balance!

Maybe you should consider a decent RDBMS (hint, PostgreSQL!) - one that obeys Codd's rules and other such logical concepts! :-) As a final point about MySQL - it doesn´t have CHECK constraints - so you have to use UNSIGNED so they can't go below zero - your transaction will fail if there´s any attempt to do so. The changes suggested by ypercube have been incorporated into the db-fiddle and this answer!