How to do this in MySQL: if field value > 0 then minus one, else let it be

mysql, sql-update

Solution

If `count` is the only column you're updating (or, you don't have other criteria specified in your where clause), then you can just do that in the where clause

UPDATE [Table] SET counts = counts - 1 WHERE counts > 0;

However, if you're updating other columns in the same query, this won't work. But you have options

UPDATE [Table] SET counts = MAX(counts - 1, 0);

or

UPDATE [Table] SET counts = CASE WHEN counts > 0 THEN counts - 1 ELSE 0 END;

Problem

Original source