Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

UPDATE tbl SET counts=counts-1 ...
like image 512
lovespring Avatar asked Dec 07 '22 04:12

lovespring


2 Answers

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;
like image 56
Peter Bailey Avatar answered Dec 10 '22 12:12

Peter Bailey


UPDATE tbl 
SET counts=counts-1 
WHERE counts > 0
like image 40
AxelEckenberger Avatar answered Dec 10 '22 13:12

AxelEckenberger