Decrement value in mysql but not negative

Question

Add another condition to update only if the field is greater 0

UPDATE table 
SET field = field - 1
WHERE id = $number
and field > 0

You could prevent the new value to drop below zero by using GREATEST(). If the value drops below zero, zero will always be greater than your calculated value, thus preventing any value below zero to be used.

UPDATE  table
SET     field = GREATEST(0, field - 1)
WHERE   id = $number

And on a side note: Please don't use mysql_* functions any more. They are deprecated and will eventually be removed from PHP. Use PDO or MySQLi instead.

The option using GREATEST will not work in newer MySQL versions, and the accepted answer can be unuseful if you want to update multiple fields instead of one. My solution for this problem is using IF:

UPDATE  table
SET     field = IF(field > 0, field - 1, 0)
WHERE   id = $number

UPDATE table SET field = case when (field - 1) >0 then (field - 1)
else field end
WHERE id = $number

 UPDATE `table_name` SET field = field-1 WHERE `id` = '".$id."' AND field > 0

Note: field's data-type should be an INTEGER.

if the field is int unsigned, below is the best:

UPDATE table 
SET field = field - 1
WHERE id = $number
and field > 0

# or
UPDATE  table
SET     field = IF(field > 0, field - 1, 0)
WHERE   id = $number