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
UPDATE `stocktable` SET `qty` = `qty` - 2 WHERE `id` = 123456 AND `qty` >= 2
Are there any better solutions?
You could check if the stock is sufficient before you do the update.
Otherwise, I think the solution you found is the only one.
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
what two queries? there's only one, the UPDATE
Putting them both together is surely the original query?