Is there any way you can tell MySQL to set value to 0 if value for unsigned column is < 0?

I have a column in MySQL that is set as unsigned; however because I do calculations on this column within MySQL it's possible (though unlikely) that we could try and set this column to a value < 0.

In this case I just want it to set it to 0 rather than try and set it to a negative value and hence cause an error.

An example would be:

UPDATE my_table SET comments=comments-3 WHERE something=123;

Is there a simple way to do this?

Use the GREATEST() function (this needs a CAST() as well):

UPDATE my_table 
SET comments = GREATEST(CAST(comments AS signed)-3, 0) 
WHERE something = 123;

or the IF() function:

UPDATE my_table 
SET comments = IF(comments>3, comments-3, 0) 
WHERE something = 123;

or a CASE expression:

UPDATE my_table
SET comments = CASE WHEN comments>3 THEN comments-3 ELSE 0 END 
WHERE something = 123;

StackExchange Database Administrators Q#157024, answer score: 3