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

Question

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?

possible duplicate of [MySQL update case help](http://stackoverflow.com/questions/6734231/mysql-update-case-help) — nawfal, Dec 06 '13 at 19:29

score 34 · Accepted Answer · edited Jun 23 '15 at 18:52

34

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)

edited Jun 23 '15 at 18:52

Erick Martim

112
1
11

answered Aug 07 '11 at 20:32

Icarus

63,293
14
100
115

Thanks @Icarus - The second solution you posted is perfect. While the first one works of course, MySQL matches every row in the table; that seems liable to cause performance loss in larger tables (*10^6 rows or more*) despite the fact that it only actually updates the rows that have changed. Can you shed some light on this? (*the condition on a union of all targeted IDs is a clean enough solution, but I'm just curious*) – Dan Lugg Aug 07 '11 at 20:51
1

@TomcatExodus: I am not sure I quite understand your question but, in general, if you have an update with no where clause the database engine will almost certainly have to perform a table scan and will hurt performance, specially on big tables. So yes, having the where clause in there should be faster but only if the id column is also an index. If it isn't, the table scan is unavoidable. – Icarus Aug 07 '11 at 22:12
Ok @Icarus - That's what I meant, the `id` column is indexed. I was referring to the output stream; without the `WHERE` clause, I get several thousands of rows matched, but only a handful are affected. The thousands being matched is the full table scan that I'd like to avoid, and *is* avoided by applying a `WHERE` clause, specifying only a union of true/false ids. Just wanted some clarification. Thanks! – Dan Lugg Aug 08 '11 at 16:39
Thanks for the tip, it appear simple but it's something that anybody can ommit! – webbi Sep 23 '13 at 17:47

score 7 · Answer 2 · answered Aug 07 '11 at 20:34

7

You can avoid field = field

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

answered Aug 07 '11 at 20:34

Nicola Cossu

54,599
15
92
98

Thanks @nick rulez - The `field = field` seemed odd at first glance too (*field = field = field?*) – Dan Lugg Aug 07 '11 at 20:52

mickmackusa · Answer 3 · 2020-12-29T21:26:46.977

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

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

3 Answers3

Linked

Related