jeudi 19 décembre 2019

MySQL: INSERT ... IF COUNT <= INVENTORY ELSE DONOTHING

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