About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Wednesday, July 4, 2012

How to convert 0 to 1 and 1 to 0 in table object


Say I am having a table ZeroOneZero with following data

select * from ZeroOneZero
name       Active
---------- ------
Prakriti   0
Aaliya     1
Falak      0
Snehal     1

(4 row(s) affected)

Now what I want is to convert 0 values in Active column to 1 and vice versa. It seems very easy to do, yes absolutely it is, but you need make sure you don’t have to use where clause in Update statement else you will end up getting something unexpected….

Let’s check out how to get done with this problem, the Case clause is the solution:

update ZeroOneZero
set Active =
      (case when Active = 0 then 1
              when Active = 1 then 0
      end)
(4 row(s) affected)

/*Now lets fire select query to see the result*/

select * from ZeroOneZero

name       Active
---------- ------
Prakriti   1
Aaliya     0
Falak      1
Snehal     0

(4 row(s) affected)

Yes we are through.

Coding a Psyche Game


Post Reference: Vikram Aristocratic Elfin Share

No comments:

Post a Comment