一个查询中的 MySQL 更新条件（UPDATE、SET 和 CASE）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6975575/
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 update conditions in one query (UPDATE, SET & CASE)
提问by Dan Lugg
I'm trying to update a set of records (booleanfields) in a single query if possible.
如果可能，我正在尝试在单个查询中更新一组记录（boolean字段）。
The input is coming from paginated radio controls, so a given POSTwill have the page's worth of IDs with a trueor falsevalue.
输入来自分页无线电控件，因此给定值POST将具有带有true或false值的页面 ID 。
I was trying to go this direction:
我试图去这个方向：
UPDATE my_table
SET field = CASE
WHEN id IN (/* true ids */) THEN TRUE
WHEN id IN (/* false ids */) THEN FALSE
END
But this resulted in the "true id" rows being updated to true, and ALLother rows were updated to false.
但这导致“真实 id”行被更新为true，所有其他行都更新为false。
I assume I've made some gross syntactical error, or perhaps that I'm approaching this incorrectly.
我假设我犯了一些严重的语法错误，或者我的处理方式不正确。
Any thoughts on a solution?
关于解决方案的任何想法？
回答by Icarus
Didn't you forget to do an "ELSE" in the case statement?
你不是忘记在case语句中做一个“ELSE”吗？
UPDATE my_table
SET field = CASE
WHEN id IN (/* true ids */) THEN TRUE
WHEN id IN (/* false ids */) THEN FALSE
ELSE field=field
END
Without the ELSE, I assume the evaluation chain stops at the last WHEN and executes that update. Also, you are not limiting the rows that you are trying to update; if you don't do the ELSE you should at least tell the update to only update the rows you want and not all the rows (as you are doing). Look at the WHERE clause below:
如果没有 ELSE，我假设评估链在最后一个 WHEN 处停止并执行该更新。此外，您没有限制您尝试更新的行；如果你不做 ELSE 你至少应该告诉更新只更新你想要的行而不是所有的行（就像你在做的那样）。看看下面的 WHERE 子句：
UPDATE my_table
SET field = CASE
WHEN id IN (/* true ids */) THEN TRUE
WHEN id IN (/* false ids */) THEN FALSE
END
WHERE id in (true ids + false_ids)
回答by Nicola Cossu
You can avoid field = field
您可以避免字段 = 字段
update my_table
set field = case
when id in (....) then true
when id in (...) then false
else field
end
