Quick Inquiry

Achieve simple "payment transaction" in MYSQL (or any rdbms) in SQL ?

Yogesh Kumar
04-May-2022
0

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

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

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