MySQL Decrementing a field (stopping at zero)

[Nigma]

Hi,

I have a products table with a stock field., when somebody purchases an item I want to decrement this value by the QTY purchased.

The only possible issue is that the stock level may not be correctly maintained. So it may be possible for the qty purchased to be greater than the stock level.

using this (Where {qty} and {id} are variables)

UPDATE products SET stock = stock - {qty} where id={id}

understandably causes the value to wrap around to the maximum allowed for an unsigned integer.

I want to avoid doing a sql read, modify write as it may be possible that another client may change the stock level mid-way through this process. Can this be done in a single line of SQL ?

Thanks,
Nigma

[Weedpacket]

The standard SQL way of doing this is to specify the restriction when the table is defined.

Create table products (
    ....
    stock int CHECK(stock>=0),
    ....
);

Any attempts to perform an update that would result in the check constraint being violated would then fail.

[Nigma]

Thanks for the reply. Unfortunately, I don't think MySQL supports the check constraints.

[johanafm]

Start transaction and select ... for update:
http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html