如何在 MySQL 更新查询中使用 If Then Else？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9723073/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use If Then Else in a MySQL update query?
提问by dido
I want to update a table in MySQL like this:
我想像这样更新 MySQL 中的表：
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:
但是上面的 SQL 是无效的语法。我也试过这个：
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?
但也是无效的语法。如何在这样的更新查询中使用 if 语句？
回答by JYelton
I think you were 99% there:
我认为你 99% 都在那里：
UPDATE table
SET A = IF(A > 20, 20, IF(A < 20 && A > 1, A, 0))
WHERE A IS NOT NULL;
Add the && A > 1to the second IF statement and your third condition is satisfied.
添加&& A > 1到第二个 IF 语句，您的第三个条件得到满足。
Edit:
编辑：
Per @Andre's comment to the question and the suggestion that the nested IFis difficult to read, you could also do this as a couple of queries that don't do any unnecessary work and are readable:
根据@Andre 对该问题的评论以及嵌套IF难以阅读的建议，您也可以将其作为几个不做任何不必要工作且可读的查询来执行：
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.
当 A 为 NULL 时，它将不满足这两个条件中的任何一个，因此无需指定 A 不为 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.
接下来，不需要@Andre 建议的第三个条件。如果 A 介于 1 和 20 之间，则保持原样。
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 <=.
最后，在 A 小于或等于 1 的情况下将 A 设置为 0 似乎不寻常。值 1 将更改为 0。如果您打算简单地将小于 1 的值（包括负值）设置为 0，那么您应该交换<为<=。
回答by jenson-button-event
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
或者更简单地说，2个语句
UPDATE Table
SET A = 20
where A > 20;
UPDATE Table
SET A = 0
where A <= 1;
