Toggle bit field with one query [Solved Again!]

[ober0330]

SQL Server 2K. I have a bit field. I need to toggle it. Can I do it with one query, rather than SELECTing it, and updating according to a logic evaluation?

[techgnome]

Just do an update query....

UPDATE SomeTable
SET MyBit = NOT MyBit
FROM SomeTable
WHERE Field = MyValue

Tg

[ober0330]

Hmm... why couldn't I have thought of that? Thanks!

[techgnome]

Sometimes it's those simple easy things we over look. I chalk it up to being a hazard of being a programmer.... we tend over analyze problems.

Tg

[ober0330]

Ok... I thought it would just work, but it's not. It doesn't like the NOT keyword:

UPDATE CompHistory SET Active = NOT Active WHERE HIST_ID IN ('10532-367-A')

[techgnome]

You know what... I bet it's because NOT wants to turn 0 to -1, which can't be handled by a bit type (0,1)....

Two things to try (and I'll try both as soon as I cna find a suitable test DB) ...

SET Active = ABS(NOT Active) ....
OR
SET Active = CASE Active WHEN 1 THEN 0 ELSE 1 END.....

Tg

[si_the_geek]

How about this?

UPDATE CompHistory SET Active = (1-Active) WHERE HIST_ID IN ('10532-367-A')

[techgnome]

OK, weird.... the CASE construct works but using NOT on the field doesn't.... seems to fly in the face of logic...

The full SQL I used to test this:

UPDATE tblQCCode
SET Enabled =CASE Enabled WHEN 1 THEN 0 ELSE 1 END
FROM tblQCCode
WHERE Description = 'Invoice Inquiry'

Tg

[ober0330]

Yeah... the CASE works, and si_the_geek's works as well, so I guess I'll take the shorter of the two. Thanks for the help fellas!

[techgnome]

His is also more portable than mine....

Tg

[ober0330]

Very true.