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

UPDATE tb开发者_高级运维l SET counts=counts-1 ...

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;

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

thanks to @Peter Bailey

this is the example with the WHERE selector .

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

Good luck .