Setting bit field based on condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Setting bit field based on condition

Author

Topic

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-06-11 : 09:45:46

This is probably pretty simple for you guys but I'm having trouble finding information anywhere, not the easiest thing to google without getting lots of useless results. Basically I need to set a bit field based on the value of an int field. Here's my query now, which it says there's a problem at '<'.

UPDATE [dbo].[Generators] SET [UseDutyCycle] = [DutyCycleIndex] <> -1

I'm guessing it has something to do with the fact that UseDutyCycle needs to be 1 or 0 and not True or False, but I don't know how to work around that.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 09:59:09

SET [UseDutyCycle] = [DutyCycleIndex] <> -1
What should that mean?

Maybe you can tell us like this:
I want UseDutyCycle set to true if ...
I want UseDutyCycle set to false if ...

Maybe example data?

No, you're never too old to Yak'n'Roll if you're too young to die.

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-06-11 : 10:31:20

Thanks for the reply. If DutyCycleIndex is -1, UseDutyCycle is 0. If DutyCycleIndex is not equal to -1, UseDutyCycle is 1.

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-06-11 : 10:35:03

Actually I just thought of something that might work, but it takes two statements. Is there an easier way? I'm pretty happy with this though, as long as it works.

UPDATE [dbo].[Generators] SET [UseDutyCycle] = 0 WHERE [DutyCycleIndex] = -1
UPDATE [dbo].[Generators] SET [UseDutyCycle] = 1 WHERE [DutyCycleIndex] <> -1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 10:38:25

UPDATE [dbo].[Generators]
SET [UseDutyCycle] =
CASE
when [DutyCycleIndex] = -1 then 0
else 1
end

No, you're never too old to Yak'n'Roll if you're too young to die.

ScottyDoesKnow
Starting Member

18 Posts

Posted - 2010-06-11 : 10:40:17

Nice, never heard of the case statement before. Thanks for your help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 10:43:49