sqlself-join

how to exchange values of same column in same table

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.

Solution 
  • UPDATE MyTable
SET Status = (CASE WHEN Status = 'Married' THEN 'Single' ELSE 'Married' END )