How to use If Then Else in a MySQL update query?

Question

I want to update a table in MySQL like this:

UPDATE Table
SET A = '20' IF A > 20
SET A = A    IF A < 20 
SET A = 0    IF A <= 1
WHERE A IS NOT NULL;

But the above SQL is not valid Syntax. I also tried this:

UPDATE table
SET A = IF(A > 20, 20, IF(A < 20, A, 0));

But is also invalid Syntax. How do I use an if statement in an update query like this?

Answer 1

I think you were 99% there:

UPDATE table
SET A = IF(A > 20, 20, IF(A < 20 && A > 1, A, 0))
WHERE A IS NOT NULL;

Add the && A > 1 to the second IF statement and your third condition is satisfied.

Edit:

Per @Andre's comment to the question and the suggestion that the nested IF is difficult to read, you could also do this as a couple of queries that don't do any unnecessary work and are readable:

UPDATE table SET A = 20 WHERE A > 20;
UPDATE table SET A = 0 WHERE A <= 1;

When A is NULL, it will not meet either of these conditions, and thus eliminates the need to specify that A not be NULL.

Next, there's no need for the third condition as @Andre suggested. If A is between 1 and 20, it gets left as-is.

Finally, setting A to 0 where A is less than or equal to 1 seems unusual. Values of 1 will be changed to 0. If you intend to simply set values less than 1 (including negative values) to 0, then you should swap < for <=.

Answer 2

UPDATE Table
SET A = Case
When A > 20 Then 20 
When A <= 1 Then 0
End
WHERE A IS NOT NULL and ( A > 20 or A <= 1 )

or more simply, 2 statements

UPDATE Table
SET A = 20 
where A > 20;

UPDATE Table
SET A = 0
where A <= 1;