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

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:

[sql]
UPDATE [TABLE]
SET isActive = 1 – isActive
WHERE [condition]
[/sql]

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: