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

Question

I want to update a table in MySQL as follows:

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 the syntax is also invalid. How to use the if statement in an update request, for example:

Answer 1

I think you were 99%:

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

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

Edit:

For @Andre's comment on the question and suggestion that nested IF hard to read, you can also do it as a couple of queries that don't do 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 any of these conditions and thus eliminates the need to indicate that A is not NULL.

Further, there is no need for the third condition proposed by @Andre. If A is between 1 and 20, it remains as-is.

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

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 simpler, 2 operators

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