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

• Yogesh Kumar
• 04-May-2022
• 0

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

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

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

* Be the first to Make Comment