一个查询中的MySQL更新条件(UPDATE，SET和CASE) [英] MySQL update conditions in one query (UPDATE, SET & CASE)

问题描述

如果可能，我正在尝试在单个查询中更新一组记录( 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

但这导致真实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更新条件(UPDATE，SET和CASE)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！