We have a stock system that uses a very simple concept of adjusting stock. It basically says:

UPDATE `stocktable` SET `qty` = `qty` - 1 WHERE `id` = 123456

which works fine in most cases, except when we're trying to remove more stock than we should. As this is an unsigned BIGINT, if we remove two items from stock but the system says that there are only one actually in stock, we end up with 18446744073709551615, simulated by:

SELECT CAST(1 AS UNSIGNED) - 2

Is there a way to avoid this? The only way we've seen so far is something like

Yeah, the problem is that there's a chance that another query could have adjusted the stock level between the two queries, is there not? Unlikely, but possible. I think we'll stick with the previous solution then. Cheers