I basically want to efficiently check if account has enough balance before deduct value from it. Let's say I have the following schema

I want to achieve the following logic

If account_balance < amount_to_charge:
   return False
else:
   udpate account_balcne = account_balance - amount_to_charge
   return True  

It's important to get the boolean value back.

So using an example, if my charge amount is 100 to account a and b above. Assume I have a magic SQL function

• charge(a, 100) -> True
  • balance of account a updated to 0
  • I get true back
• chrage(b, 50) -> False

  • balance not updated

  • i get false back

How might one achieve this in SQL, ideally in one round trip?

EDIT: I know how to do a conditional update by doing

update set account_balance = account_balance - x where accoutn_balance > x and account_id = 123

However, how might one also get a boolean value?

CodePudding user response：

First I want to say that creating a function for such things is really no good way. It would be much better to just create a constraint (see also the answer by Akina). But anyway, to answer your question: You can create a function like this:

CREATE FUNCTION yourfunction
(account VARCHAR(100), 
amount_to_charge DECIMAL
)RETURNS VARCHAR(100)
BEGIN
DECLARE account_balance DECIMAL;
SELECT balance INTO account_balance FROM yourtable
WHERE account_id = account;
IF account_balance < amount_to_charge THEN
RETURN (FALSE);
ELSE
UPDATE yourtable SET balance = account_balance - amount_to_charge 
WHERE account_id = account;
RETURN (TRUE);
END IF;
END

I created an example here which shows this is working correctly: db<>fiddle

Please note there is no "real" boolean in MYSQL, so this function will return 0 or 1. You have to convert it to true or false later on if necessary.

CodePudding user response：

1. Add CHECK constraint which checks that the balance value is not negative:

ALTER TABLE tablename
    ADD CHECK (balance >= 0);

2. Perform the transaction as solid statement:

UPDATE tablename
SET balance = CASE account_id WHEN @sender_id   THEN balance - @transfer
                              WHEN @receiver_id THEN balance   @transfer
                              ELSE balance
                              END
WHERE account_id IN (@sender_id, @receiver_id);

The whole transfer will either be performed successfully (and both accounts will be updated) or will produce an error 3819 (and none account will be updated).

DEMO