I have a warehouse system where you can insert or remove products with a barcode and a count number. Now I want to check first if there are enough products in the inventory before executing the transaction. So something like: IF enough products in inventory THEN INSERT transaction ELSE donothing (or show error)
I tried this code:
SELECT CASE WHEN 7 <= (SELECT i.count FROM inventory i WHERE barcode = '123')
THEN
INSERT INTO transactions
(companyid,barcode,fromlocation,tolocation,count)
VALUES (1,'123','Warehouse','Factory',7)
END
But it says:
1. Unexpected keyword. (near "INTO" at position 88)
2. Unexpected end of CASE expression (near " " at position 87)
3. Unrecognized keyword. (near "END" at position 198)
So obviously this is the wrong syntax, but i searched everywhere and could not find an if statement which provides this scenario.
Can somebody tell me how to write the correct if statement?
I know that I could make a select statement first, compare the values and just then insert the transaction (with a MySQL beginTransaction and commit), but I am pretty sure that there has to be another way. (When I talk about transaction I don't talk about the transaction from MySQL but about a transaction from my database)
Thank you
Aucun commentaire:
Enregistrer un commentaire