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

Case with Update statement Or Conditional Update

There are certain situation where we need some updation  on the basis of condition. For example  if name contain ‘Kruthika’ update it to ‘Kruthik’.

Our table is containing the following records

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

You might be thinking this update can be accomplished with the help of were condition in update statement. Correct it goes like this

update user_tab

set user_name = 'Kruthik'

where user_name = 'Kruthika'

(1 row(s) affected)

select * from user_tab

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthik                                            1

2           Orpita                                             1

3           Aalap                                              1

4           Trishona                                           1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjan                                           1

(11 row(s) affected)

But consider a situation where you need to update ‘Trishona’ with ‘Trishon’ and ‘suranjan’ with ‘suranjana’.

Is it possible with WHERE clause in Update statement. ANSWER IS NO.

Then what is option left for such update, Here comes CASE, let see how this update can be done with CASE

update user_tab

set user_name = (case user_name

                              when 'Trishona' then 'Trishon'

                              when 'suranjan' then 'suranjana'

                              else user_name end)

user_id     user_name                                          active

----------- -------------------------------------------------- ------

1           Kruthika                                           1

2           Orpita                                             1

3           Aalap                                              1

4           Trishon                                            1

5           Shobna                                             1

6           Eekalabya                                          1

7           Phaneesh                                           1

8           Kishalaya                                          1

9           Mrinmoy                                            1

10          Satyajit                                           1

16          suranjana                                          1

(11 row(s) affected)

I really like this keyword a lot of thing can be accomplished with CASE.

Post Reference: Vikram Aristocratic Elfin Share