Query to increment or decrement value in MySQL ignoring negative values

Solution:

UPDATE table_name
SET value = value - 1
WHERE value > 0;

You can add more conditions in the WHERE clause if you want.

If you want to prevent that new value from turning negative (less then 0 if you keep decreasing), you could use GREATEST function. If the value drops below zero, zero will always be greater than your calculated value, thus preventing any value below zero to be used.

GREATEST() function MySQL

Syntax

GREATEST(value1,value2,...)

With two or more arguments, it returns the largest (maximum-valued) argument.

Example

mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0

GREATEST() returns NULL if any argument is NULL.

Main example with GREATEST()

UPDATE  table_name
SET value = GREATEST(0, value - 1)
WHERE   id = 1;

Share this post