How to Assign 0 if Calculated Value is <0 within CASE-CodePudding

I have a MySQL table named users containing simple counts for all users.

user_id	counter1	counter2
1	0	5
2	1	6
3	2	7
4	3	8

I would like to run a single query that updates the counter1 values to counter1-# or 0 (# can be any whole number), whichever is greater for possibly multiple users. The counter1 column is an unsigned int type.

I am executing the following query to update users 2 and 3:

UPDATE 
   users 
SET 
   counter1 =   CASE user_id 
                WHEN 2 THEN GREATEST(counter1 - 2, 0) 
                WHEN 3 THEN GREATEST(counter1 - 5, 0) 
                ELSE counter1 END
WHERE user_id IN(2, 3);

Running the above query returns an error:

BIGINT UNSIGNED value is out of range in '(`user`.`counter1` - 2)'

The end result I'm trying to aim for is for both user 2 and user 3 to have a minimum counter1 value of 0 after the query is executed since in both users' cases, subracting 2/5 from their counter1 values will be a negative number, which of course won't work.

Is there a way to do this with a single query?

CodePudding user response：

The problem is that the expression counter1 - 2 produces a negative value when counter1 is less than 2. But since it's declared UNSIGNED, the result of expressions that use it are also unsigned, so negative values are not allowed.

Instead of subtracting from it, use an IF() expression to prevent calculating these invalid values.

CASE user_id
    WHEN 2 THEN IF(counter1 > 2, counter1 - 2, 0)
    WHEN 3 THEN IF(counter1 > 5, counter1 - 5, 0)
    ELSE counter1
END