MySQL - Deduct from two columns with a remainder 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.
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( (@remainder := w.balance - z.amount) &gt;= 0, @remainder, 0 )
     , w.otherBalance = IF( @remainder &gt;= 0, w.otherBalance, w.otherBalance + @remainder )
;  


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 @remainder becomes negative therefore I've added it to the otherBalance instead of subtracting it.