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'.


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.

  • 2
    Unclear question - can you please put in more than one example of data before, what happens and the final result and the logic you used to get that result? Where, for example, is the "amount" that you speak of? – Vérace Apr 4 '18 at 18:58
  • Verace, Check my updated question. – Urbanleg Apr 5 '18 at 12:13

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
  balance_2 =
      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))
  balance_1 = 
      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

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!

  • 1
    The problem is not transactional. It's that the update of the first column is done first, and then the update of the 2nd column "sees" the updated value of the first column - while it shouldn't. It should be seeing the old value. In other words, the updates of the columns happen one after the other and not all together. – ypercubeᵀᴹ Apr 5 '18 at 18:57
  • 1
    If you do the update in reverse (column) oder, then it works: db-fiddle.com/f/bJHP6fbK9nkRevjuNzhSH/0 – ypercubeᵀᴹ Apr 5 '18 at 19:01
  • 1
    It is wrong, true (or non-standard). But it is not 2 transactions. it's still one transaction ;) – ypercubeᵀᴹ Apr 5 '18 at 19:02
  • 1
    The behaviour is documented in the UPDATE statement docs: "The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL. UPDATE t1 SET col1 = col1 + 1, col2 = col1; Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order. " – ypercubeᵀᴹ Apr 5 '18 at 19:06
  • 2
    And feel free to put the info in your answer ;) – ypercubeᵀᴹ Apr 5 '18 at 19:25

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.