Click here to Skip to main content
14,446,960 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have dat like below

Col
1
2
2
2
2
1
1
1
1

Here if want update where ever the values 2 that should be changed as 1 and where the value is 1 that should be 2 for that how can i write the query with update



Update tablename set col =1 where col =2 
Update tablename set col =2 where col =1

I want to put both in single command.Could you please help me.
Posted
Updated 11-Jan-16 1:48am
v2
Comments
Michael_Davies 11-Jan-16 6:54am
   
Must be homework..

Problem is all records will end up = 2 if you do it as in the question...bit like getting the fox, cabbage and rabbit across a river.
Andy Lanng 11-Jan-16 6:54am
   
If you perform the first Query then all 2's will be 1's, but the second sets them back to 2's. I'm guessing that's why you need to do it all in a single query?
Michael_Davies 11-Jan-16 7:01am
   
Other way round, first sets all to 1, second sets all to 2...
Andy Lanng 11-Jan-16 7:02am
   
doh yeah #^_^#
corrected.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If the problem is truly as simple as stated, then use "case":

update tablename
set col = case col
          when 1 then 2
          else 1 end


That also takes care of the where clause.

I double you problem is quite that simple so make sure that you check the query for yourself.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100