CODEWITHSUNDEEP
sqlt-sql
The Muffin Man
The Muffin Man

Reputation: 20004

advanced sql update command

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">

Upvotes: 1

Views: 613

Answers (3)

Dustin Laine
Dustin Laine

Reputation: 38503

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.

Upvotes: 2

Ian Henry
Ian Henry

Reputation: 22403

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.

Upvotes: 1

zerkms
zerkms

Reputation: 254906

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

Upvotes: 1

Related Questions