MySQL CASE...WHERE...THEN 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8348037/
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
MySQL CASE...WHERE...THEN statements
提问by CDspace
I have a MySQL UPDATE statement that uses a CASE clause
我有一个使用 CASE 子句的 MySQL UPDATE 语句
UPDATE partsList SET quantity =
CASE
WHEN partFK = 1 THEN 4
WHEN partFK = 2 THEN 8
END
WHERE buildFK = 1;
The above statement works. Yet when I remove one of the WHEN statements, it breaks and the error indicates the CASE clause isn't returning anything. Is it that the CASE clause must have more than one WHEN to function.
上面的语句有效。然而，当我删除其中一个 WHEN 语句时，它会中断并且错误表明 CASE 子句没有返回任何内容。是不是 CASE 子句必须有多个 WHEN 才能起作用。
I don't know beforehand how many updates I'll need, so I'm trying to build a single update statement that can handle one or many updates.
我事先不知道我需要多少次更新，所以我试图构建一个可以处理一个或多个更新的更新语句。
Thanks for any insights you can provide.
感谢您提供的任何见解。
回答by recf
It isn't that the CASEmust have more than one, WHEN...THEN, it's that it must handle all the date you give it.
不是CASE必须有多个WHEN...THEN，而是它必须处理您给它的所有日期。
If you removed one of the clauses, you leave a hole. e.g.
如果您删除了其中一个子句，则会留下一个漏洞。例如
UPDATE partsList SET quantity =
CASE
WHEN partFK = 1 THEN 4
END
WHERE buildFK = 1;
With this update statement, if parkFKis 2, then the update fails because the CASE can't handle the input.
使用此更新语句，如果parkFK为 2，则更新失败，因为 CASE 无法处理输入。
You can either limit your source data by adding another line to your where-clause (e.g. AND partFK in (1,2)), or you could add an ELSEto the case expression.
您可以通过在 where 子句（例如AND partFK in (1,2)）中添加另一行来限制源数据，也可以ELSE在 case 表达式中添加。
UPDATE partsList SET quantity =
CASE
WHEN partFK = 1 THEN 4
WHEN partFK = 2 THEN 8
ELSE 12
END
WHERE buildFK = 1;
However, based on the SQL statement you've shown, there is probably a better way. Presumably, partFK is a foreign-key to some other table. Can you pull the value for quantityfrom there?
但是，根据您展示的 SQL 语句，可能有更好的方法。据推测，partFK 是其他表的外键。你quantity能从那里提取价值吗？
回答by Kenaniah
Add ELSE NULLor something before the ENDof the case. See http://dev.mysql.com/doc/refman/5.0/en/case-statement.htmlfor more info.
在案例ELSE NULL之前添加或其他内容END。有关更多信息，请参阅http://dev.mysql.com/doc/refman/5.0/en/case-statement.html。
