How to update an unsigned record safely against negative value?

Question

I have a trigger like this which keeps the total value in the parent table:

UPDATE posts p SET total_comments = GREATEST(total_comments - 1, 0) WHERE old.post_id = p.id

I've used GREATEST() to avoid negative numbers, but still, I get this error if the current value of total_comments is 0 and then the trigger executes:

#1690 - BIGINT UNSIGNED value is out of range in (mydb.p.total_comments - 1)

Any solution?

Answer 1

From Out-of-Range and Overflow Handling:

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, an error results

In your case total_comments is defined as UNSIGNED and if its value is 0 then the evaluation of the expression total_comments - 1 throws an error.

A workaround would be to compare total_comments to 1 inside GREATEST() and after that do the subtraction:

UPDATE posts p 
SET total_comments = GREATEST(total_comments, 1) - 1 
WHERE old.post_id = p.id

Answer 2

What about using where clause to avoid updating it if it's already 0. Try this:

UPDATE posts p
SET total_comments = total_comments - 1
WHERE old.post_id = p.id
  AND total_comments > 0