Toggle states like active, deleted, etc. (bitwise negation in SQL)

By | December 2, 2006

Just a little trick which might be useful. It’s not new but I always enjoy using it and therefore its worth a quick post. In a lot of cases you find records in your database which hold a bit value 1/0 for fields like isActive, deleted, approved, etc. Usually they are toggled from 0 to 1 or the other way round. Here comes a nice and quick solution to solve this toggling…

Let’s say we have a bit field called “isActive” which holds 0 if the user is inactive and 1 if he/she is active. For our presentation we just want one button in order to achieve this state change and the underlying DB access should be done with one SQL statement if possible. Okay so here we go:

  1. UPDATE [TABLE]
  2. SET isActive = 1 - isActive
  3. WHERE [condition]

Voila thats it, this SQL query toggles the isActive field of the user by using bitwise negation. The current state is being deducted from 1 and therefore 1 minus 0 results in 1 and 1 minus 1 in 0. Invert done! Needless to say, this solution is not limited to SQL and can be used everywhere where negation is needed.

I enjoy this solution because it’s so handy. have fun!

Category: SQL

Leave a Reply

*

* Copy This Password *

* Type Or Paste Password Here *

135,345 Spam Comments Blocked so far by Spam Free Wordpress