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 将具有页面的 ID 价值，其值为 true 或 false.

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.

对解决方案有什么想法吗?

Any thoughts on a solution?

推荐答案

你是不是忘了在case语句中做一个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屋！