2

I am trying to update a column in a record to true to indicate that the record is the one active in the table. However, by updating this record, I must then update all other records for that column to false. Is there a way to do this in one SQL statement? I can do it in two statements like this:

UPDATE My_Table
SET is_group_active = 0

UPDATE My_Table
SET is_group_active = 1
WHERE group_id = 2;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
nikotromus
  • 1,015
  • 16
  • 36
  • 1
    You want to guarantee only one record to be active at anytime? Why not create an `ActiveGroup`Table and store the active `group_id` (max one single record or select the last (by ìd or timestamp)). In your queries you get the value by counting the `group_id` in the new table – ComputerVersteher Dec 21 '18 at 15:21

2 Answers2

8

You could use a case expression:

UPDATE my_table
SET    is_group_active = CASE group_id WHEN 2 THEN 1 ELSE 0 END;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
2

I would write this as:

UPDATE t
    SET is_group_active = (CASE group_id WHEN 2 THEN 1 ELSE 0 END)
    WHERE is_group_active <> (CASE group_id WHEN 2 THEN 1 ELSE 0 END);

Or perhaps:

UPDATE t
    SET is_group_active = (CASE group_id WHEN 2 THEN 1 ELSE 0 END)
    WHERE is_group_active = 1 OR group_id = 2

There is no need to update rows that already have the correct value. (Note: The logic would be slightly more complicated if is_group_active can take on NULL values).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Except that I also have to update if a user marks the record as inactive. It's possible that the entire table has no active records. There won't be more than maybe 10 records in the table at the most, so I think the first solution will work. thanks though. +1! – nikotromus Dec 21 '18 at 15:16