Using IF-THEN-ELSE statement in a mySQL transaction

Question

I am learning MySQL and i have a simple transaction to make. Need to deduct quantity from stock, if there is sufficient stock for a specific item. Else, need to roll back the whole transaction.

I have tried the following code :

SET @item_code = 'PEN-001';
set @balance = (SELECT quantity_in_hand FROM items WHERE item_code = 'PEN-001');
-- ------------------------------------------------
DELIMITER //
START TRANSACTION;

IF (@balance) >= 0 THEN 
BEGIN
    UPDATE items SET quantity_in_hand = @balance - @purchaseqty WHERE item_code = 'PEN-001'
END;
ELSE 
BEGIN
    ROLLBACK;
    SELECT 'Insufficient Stock';
END;
END IF;

COMMIT;

DELIMITER ;

When I run this query in MySQL, nothing happens, there is no action output or error displayed either. Is this the right approach? And, What is the issue with this code?

*Need to deduct quantity from stock, if there is sufficient stock for a specific item. Else, need to roll back the whole transaction.* Simply add according constraint to `stock` column which does not allow it to be negative. Or make its datatype UNSIGNED. While performing an attempt to make an operation which will make the value negative (i.e. not enough) the query fails and all its changes rollbacks automatically. I.e. you perform UPDATE (w/o any transaction or checking) but capture an error. — Akina, Jan 26 '23 at 06:02
Does this answer your question? [Using an if statement outside a stored procedure](https://stackoverflow.com/questions/26358397/using-an-if-statement-outside-a-stored-procedure) — ysth, Jan 26 '23 at 06:10
@Akina Yup, that's definitely a better way to keep the stock from going negative. However, The code that I wrote is just to simplify the question. let's say that the quantity should not go below a certain inventory level, which is stored in another table. If there are such checks then I think transaction would be a better choice, is it? but i am not able to make the IF-ELSE-THEN work. Just trying to learn, thank you for your reply. — Honey Trivedi, Jan 26 '23 at 06:14
*let's say that the quantity should not go below a certain inventory level, which is stored in another table.* In this case you may move this checking logic to the trigger. If the stock remaining quantity is less then allowed one (this is checked with according query) then trigger generates SIGNAL which breaks the query and reverts all changes. — Akina, Jan 26 '23 at 06:21
@Akina I tried using the code inside a stored procedure instead of trigger (suggested in the thread linked by @ysth). And, after a few tweaks, it is working :) — Honey Trivedi, Jan 26 '23 at 07:18
That is, you do the operation "by hands", which can be performed automatically.. it's your choice. — Akina, Jan 26 '23 at 08:49

score 0 · Answer 1 · answered Jan 26 '23 at 10:44

You could do:

UPDATE items 
SET quantity_in_hand = quantity_in_hand - @purchaseqty 
WHERE item_code = 'PEN-001' and quantity_in_hand>=@purchaseqty

and then check ROW_COUNT()

The transaction in your example does nothing as rollback has nothing to do and the update when it happens, is atomic by default.

Using IF-THEN-ELSE statement in a mySQL transaction

1 Answers1