Swap Values in SQL

There is a way to swap bit values.I saw it in one of my colleagues mahendra blog.

Later I thought if I want to swap non bit values. May it be a string or an integer. Then how to do it. There may be numerous ways. But one way which I tried is as below.

Step 1 . Make a table named data and have two columns id(pk autonumber) and val(int)

For testing put come data

Step 2. Put some raw data eg, 1,1;2,2;3,1;4,2;5,1;6,2 etc.

Step 3.

create a procedure names uspSwapData

Create procedure uspSwapData

as

declare @mydata table

(

id int)

insert into @mydata

Select ID from data where val=2

update data set val=2 where

ID in (select id where val=1)

-- or you can use this query instead of the above one

-- update data set val=2 where val=1

update data set val=1 where id in (select id from @mydata)

Now it is done. you will get the Val swapped with 1 and 2

Enjoy ... I hope this will help many SQL loving souls...