Toggle bit field with one query [Solved Again!]

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?

Just do an update query....

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

Tg

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

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

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')

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

How about this?

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

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:

Code:

---

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

---

Tg

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! :)

His is also more portable than mine....

Tg

Very true.