MySQL conditionally UPDATE rows' boolean column values based on a whitelist of ids

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

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)

You can avoid field = field

update my_table    set field = case        when id in (....) then true        when id in (...) then false        else field    end

You can avoid the use of a case block for this task because you are setting a conditional boolean value.

Declare all ids that should be changed in the WHERE clause.

Declare all of the true id values in the SET clause's IN() condition. If any given id is found in the IN then the boolean value will become true, else false.

Demo

TABLE `my_table` (  `id` int(10) UNSIGNED NOT NULL,  `my_boolean` boolean) ENGINE=MyISAM DEFAULT CHARSET=latin1;ALTER TABLE `my_table`  ADD PRIMARY KEY (`id`);  INSERT INTO `my_table` VALUES(1,true),(2,false),(3,true),(4,false),(5,true),(6,false);UPDATE my_tableSET my_boolean = (id IN (2,4))WHERE id IN (2,3);SELECT * FROM my_table;

Output:

id  my_boolean1   true2   true       #changed3   false      #changed4   false5   true6   false