Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
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 0: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

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

SQL
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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900