Mysql – How to set a constant number as the lowest number in a column

MySQLupdate

Here is my query?

UPDATE users 
SET reputation = reputation - (CASE WHEN id = 29 THEN 2 
                                    WHEN id = 30 AND NOT 0 THEN 15
                                    ELSE 0 
                               END)
WHERE id IN (30, 29);

Sometimes it throws this error:

#1690 – BIGINT UNSIGNED value is out of range in '(spy.users.reputation – (case when (spy.users.id = 29) then 2 when ((spy.users.id = 30) and (not(0))) then 15 else 0 end))'

As you see in the error message, reputation column is UNSIGNED, and I need to keep it 1 at least .. something exactly like stackoverflow: if a user has 1 rep and he gets a downvote, his rep will still remain 1. Well how can I fix that update query to prevent failing in such a case?

Noted that using greatest(): doesn't make any different. I mean this won't work either:

UPDATE users 
    SET reputation = GREATEST(reputation -
                               (CASE WHEN id = 29 THEN 2 WHEN id = 30 AND NOT 0 THEN 15
                                     ELSE 0 END), 0)
WHERE id IN (30, 29);

Best Answer

SET reputation = 
    GREATEST(1,
        CAST(reputation AS SIGNED) - ( ... )
            )
Related Question