advanced sql update command

Question

Lets say I have a table that has a bit column named Active. By default, the column will contain a value of false for every row except one. When I choose to use a gridview to update a new row and have its 'Active' column change from false to true...

How can I modify the following update command to update all previous rows to false when a new row is to be set to true. ( I only want to have one row be set to active(true) at a time in this table).

UpdateCommand="UPDATE [RbSpecials] 
                  SET [Name] = @Name, 
                      [Description] = @Description, 
                      [Active] = @Active 
                WHERE [ID] = @ID">

Answer 1

UPDATE [RbSpecials]
SET [Active] CASE WHEN [ID] = @ID THEN 1 ELSE 0 END

Sorry for possible mistakes, I have not been working with sql server long ago, but I hope you'll get the idea

Also in case of optimisation this WHERE clause can be added

WHERE [ID] = @ID OR [Active] = 1

Answer 2

UPDATE [RbSpecials] 
SET 
    [Name] = @Name, 
    [Description] = @Description, 
    [Active] = @Active WHERE [ID] = @ID

UPDATE [RbSpecials] 
SET 
    [Active] = 0 WHERE [ID] != @ID

You could create a stored procedure that does this and then just pass @Name, @Description and @ID.

Answer 3

In theory, you could do it like this:

UPDATE [RbSpecials] SET 
    [Name] = case when [ID] = @ID then @Name else [Name] end,
    [Description] = case when [ID] = @ID then @Description else [Description] end,
    [Active] = case when [ID] = @ID then 1 else 0 end
FROM [RbSpecials]

But it would be more efficient (I have not profiled, but it seems intuitive) and definitely far more readable to do this in two statements, per Dustin Laine's answer.