14,446,960 members
Rate this:
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

Posted
Updated 11-Jan-16 1:48am
v2
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.

Rate this:

## 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.