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

user_409279 0
Viewed 38364

I'm trying to update a set of records (boolean fields) in a single query if possible.

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

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?

conditional-statements case sql-update boolean mysql
3 Answers

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_table
SET my_boolean = (id IN (2,4))
WHERE id IN (2,3);

SELECT * FROM my_table;

Output:

id  my_boolean
1   true
2   true       #changed
3   false      #changed
4   false
5   true
6   false
user_29434030
Related