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

Question

UPDATE tbl SET counts=counts-1 ... 

Answer 1

If count is the only column you are updating (or you have no other criteria specified in the where clause), you can simply do this in the where clause

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

However, if you update other columns in the same query, this will not 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; 

Answer 2

 UPDATE tbl SET counts=counts-1 WHERE counts > 0 

Answer 3

thanks @Peter Bailey

This is an example with a WHERE selector.

 UPDATE [tbl_multimedia] SET [m_publish] = CASE WHEN [m_publish] = 0 THEN '1' ELSE '0' END WHERE id='1' 

Good luck.