如何在MySQL更新查询中使用If Then Else？ [英] How to use If Then Else in a MySQL update query?

问题描述

我想像这样更新MySQL中的表：

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;

但上述SQL无效语法。我也试过这个：

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));

但语法也无效。如何在这样的更新查询中使用if语句？

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

推荐答案

我认为你在99％那里：

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;

添加&& A> 1 到第二个IF语句，第三个条件满足。

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

编辑：

Per @ Andre对问题的评论以及嵌套 IF 难以阅读的建议，你也可以这样做不做任何不必要工作且可读的查询：

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;

当A为NULL时，它不会满足这些条件中的任何一个，因此无需指定A不是NULL。

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

接下来，没有必要像@Andre建议的第三个条件。如果A介于1和20之间，它将保持原样。

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

最后，将A设置为0，其中A小于或等于1似乎不正常。值1将更改为0.如果您只想将小于1的值（包括负值）设置为0，则应将< 交换为< = 。

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

这篇关于如何在MySQL更新查询中使用If Then Else？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！