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!