MySQL根据ID白名单有条件地更新行的布尔列值 [英] MySQL conditionally UPDATE rows' boolean column values based on a whitelist of ids
问题描述
如果可能,我正在尝试在单个查询中更新一组记录( boolean
字段).
I'm trying to update a set of records (boolean
fields) in a single query if possible.
输入来自分页的无线电控件,因此给定的 POST
将具有值为 true
或 false
值的页面ID
The input is coming from paginated radio controls, so a given POST
will have the page's worth of IDs with a true
or false
value.
我正试图朝这个方向前进:
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
但这导致"true id"行更新为 true
,而其他 ALL 行更新为 false
.
But this resulted in the "true id" rows being updated to true
, and ALL other rows were updated to false
.
我认为我在语法上犯了一些重大错误,或者我可能错误地采用了这种方法.
I assume I've made some gross syntactical error, or perhaps that I'm approaching this incorrectly.
对解决方案有何想法?
推荐答案
您是否还忘了在案例声明中添加"ELSE"?
Didn't you forget to do an "ELSE" in the case statement?
UPDATE my_table
SET field = CASE
WHEN id IN (/* true ids */) THEN TRUE
WHEN id IN (/* false ids */) THEN FALSE
ELSE field=field
END
在没有ELSE的情况下,我假设评估链在最后一个WHEN停止并执行该更新.另外,您并没有限制您尝试更新的行;如果您不执行ELSE,则至少应告诉更新仅更新所需的行,而不更新所有行(如您所做的那样).查看下面的WHERE子句:
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:
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)
这篇关于MySQL根据ID白名单有条件地更新行的布尔列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!