mysql sql if then_如何在MySQL更新查询中使用If Then Else？

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?

解决方案

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 <=.