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?