0

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);
2
  • 1
    NOT 0 is always "true"; why is that part of the expression?
    – Rick James
    Commented Aug 2, 2016 at 0:32
  • @RickJames Please take a look at this.
    – stack
    Commented Aug 2, 2016 at 8:12

2 Answers 2

1
SET reputation = 
    GREATEST(1,
        CAST(reputation AS SIGNED) - ( ... )
            ) 
2
  • Could you please elaborate? Commented Aug 2, 2016 at 8:10
  • @dezso - The CAST treats reputation as a signed INT, thereby avoiding the error message. The GREATEST says "take 1 or that other expression, whichever is larger". This prevents actually trying to store a negative number in the UNSIGNED reputation. The 1 comes form the OP's requirements. (Other answers read that as 0.)
    – Rick James
    Commented Aug 2, 2016 at 16:41
0

[Based on Rick Jame's answer, but expands to include original question and explains the meaning]

Take the GREATEST clause and wrap it within a CAST to return it back to an unsigned type before it is used to update the field.

The 1 represents the greatest is the lowest value that can be stored in the reputation field, and is the number you can modify if you want to set a minimum value.

UPDATE users 
SET reputation = CAST(
    GREATEST(1,
        (reputation - (
            CASE
                WHEN id = 29 THEN 2
                WHEN id = 30 AND TRUE/FALSE THEN 15
                ELSE 0
            END
            ))
        )
    AS UNSIGNED)
WHERE id IN (30, 29)

The TRUE/FALSE piece is where the PHP script can inject the correct true or false value instead of the mySQL engine evaluating a boolean expression each time it executs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.