2

I want to know how I can exchange values of a same column in the same table itself in one query.

For example, Table is like below.

  SerialNo         Status
      1           Married
      2           Single
      3           Married

Now, Result what i want is that "Married" should be converted into Single and "Single" should be converted into Married.

Expected:

  SerialNo         Status
      1           Single
      2           Married
      3           Single

This should be accomplished in ONE query only. Is it possible to do so with a single query ? If yes, Help me.

Thanks in advance.

Justin
  • 9,634
  • 6
  • 35
  • 47
Alpesh Prajapati
  • 1,593
  • 2
  • 18
  • 38
  • maybe an answer can be found here [link](http://stackoverflow.com/questions/9162671/multiple-sql-update-statements-in-single-query) ? – Alexander Kuzmin Apr 26 '13 at 05:23
  • I think your question is not upto exchange this value. right? are you asking for n number exchange ? – Anvesh Apr 26 '13 at 05:27
  • @Anvesh yaa...Basically i need to swap the values. Wherever it is married, it should be single and where it is single, it should be married. No matter how many rows are in the column. – Alpesh Prajapati Apr 26 '13 at 05:31

3 Answers3

6
UPDATE MyTable
SET Status = (CASE WHEN Status = 'Married' THEN 'Single' ELSE 'Married' END )
Vijendra Singh
  • 628
  • 3
  • 13
1

Use this:

SET Status = CASE WHEN Status = 'Married' THEN 'Single'
                  WHEN Status = 'Single'  THEN 'Married' 
                                          ELSE 'Unknown' END
Pieter Geerkens
  • 11,775
  • 2
  • 32
  • 52
0

This can be donw using case statement in select clause as well

select 
serialNo,
case when status= 'Married' then 'Single'
      when status= 'Single' then 'Married' 
end as status
from table1;
DB08
  • 151
  • 6