stack stack - 4 months ago 9
SQL Question

How can I set a constant number as the lowest number in a column?

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?

Answer

Use greatest():

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

EDIT:

Try this version:

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

The problem is the intermediate calculation. I thought MySQL might do the intermediate calculation correctly, and then fail on the assignment.