CODEWITHSUNDEEP
sqlsql-serversql-update
nikotromus
nikotromus

Reputation: 1064

how to update one record column to true, all else false

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;

Upvotes: 2

Views: 7219

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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).

Upvotes: 2

Mureinik
Mureinik

Reputation: 311563

You could use a case expression:

UPDATE my_table
SET    is_group_active = CASE group_id WHEN 2 THEN 1 ELSE 0 END;

Upvotes: 8

Related Questions