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

Dustin Laine · Accepted Answer · 2010-09-02 14:13:08Z

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.

edited Sep 2, 2010 at 14:13

answered Sep 2, 2010 at 0:12

Dustin Laine

38.7k10 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ian Henry Over a year ago

You can do it with one command (see my answer) but this is a much better solution.

The Muffin Man Over a year ago

Dustin I modified the query you gave me to check if the Active is already set to 1, if it is not it wont remove active status from any other rows. UPDATE [RbSpecials] SET [Active] =0 WHERE [ID] != @ID and @Active = 1

zerkms · Accepted Answer · 2010-09-02 00:10:15Z

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

answered Sep 2, 2010 at 0:10

zerkms

256k73 gold badges447 silver badges551 bronze badges

1 Comment

bobs Over a year ago

Almost got it, except for a missing =. SET [ACTIVE] = CASE... Not bad for being away from SQL Server.

Ian Henry · Accepted Answer · 2010-09-02 00:17:32Z

1

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.

answered Sep 2, 2010 at 0:17

Ian Henry

22.4k4 gold badges54 silver badges61 bronze badges

1 Comment

Dustin Laine Over a year ago

Interesting approach, as you said not as efficient but an alternative.

Collectives™ on Stack Overflow

advanced sql update command

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related