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;
database decrement increment sql clause sql query