Switching values in a column with one update statement

Question

You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one update query to resolve this.

This question was asked at a recent interview I had, and I'm going into more interviews that may have similar questions so I wanted to get an idea of how to handle this.

Answer 1

You want to use a CASE expression of some type.

In SQL Server the code would look like this:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W' 
                  WHEN gender = 'W' THEN 'M'
                  ELSE gender END

Edit: As stated in the comments (and some of the other answers) the ELSE isn't necessary if you put a WHERE clause on the statement.

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W' 
                  WHEN gender = 'W' THEN 'M' END
WHERE gender IN ('M','W')

This avoids unnecessary updates. The important thing in either case is to remember that there are options other than M & W (NULL for example) and you don't want to put in mistaken information. For example:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W' 
                  ELSE 'M' END

This would replace any NULLs (or other possible genders) as 'M' which would be incorrect.

---

A couple of other options would be

/*Simple form of CASE rather than Searched form*/
UPDATE TableName
SET    gender = CASE gender
                  WHEN 'M' THEN 'W'
                  WHEN 'W' THEN 'M'
                END
WHERE  gender IN ( 'M', 'W' );

And a more concise

/*For SQL Server 2012+*/
UPDATE TableName
SET    gender = IIF(gender = 'M', 'W', 'M')
WHERE  gender IN ( 'M', 'W' ); 

Answer 2

In Oracle you could use a CASE as the other answers have:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W' 
                  WHEN gender = 'W' THEN 'M'
             END
WHERE gender in ('M','W');

You could also use a DECODE:

UPDATE TableName SET gender = DECODE(gender,'M','W','W','M')
WHERE gender in ('M','W');

Answer 3

For switching between just two values, you could also try this trick, which does not use a CASE expression (assuming Transact-SQL here):

UPDATE
  YourTable
SET
  Gender = CHAR(ASCII('M') + ASCII('W') - ASCII(Gender))
WHERE
  Gender IN ('M', 'W')
;

Depending on the current value of Gender, ASCII(Gender) will cancel out either ASCII('M') or ASCII('W'), leaving the other code to be transformed by the CHAR() function back to the corresponding character.

I am leaving this just for comparison, though. While this option may have a pretence of elegance to it, a solution using a CASE expression would arguably be more readable and thus easier to maintain, and it would definitely be easier to expand to more than two values.

Answer 4

You can do it with a case ... when expression:

mysql> select * from genderswap;
+--------+
| gender |
+--------+
| F      |
| F      |
| M      |
| M      |
| M      |
| M      |
| M      |
+--------+
7 rows in set (0.00 sec)

mysql> 
mysql> UPDATE genderswap SET gender = case 
    ->                                when gender='M' then 'F' 
    ->                                when gender='F' then 'M'
    ->                                end
    -> WHERE gender IN ('M', 'F');
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> 
mysql> select * from genderswap;
+--------+
| gender |
+--------+
| M      |
| M      |
| F      |
| F      |
| F      |
| F      |
| F      |
+--------+
7 rows in set (0.00 sec)

mysql> 

Answer 5

I would use an update with a case expression.

DECLARE @Test TABLE
    (
      Name VARCHAR(100) NULL
    , Gender CHAR(1) NULL
    );

INSERT  INTO @Test
        ( Name, Gender )
VALUES  ( 'Jonathan', 'W' )
         ,
        ( 'Kelly', 'M' );

SELECT  Name
      , Gender
FROM    @Test;

UPDATE  @Test
SET     Gender = CASE WHEN Gender = 'M' THEN 'W'
                      ELSE 'M'
                 END;

SELECT  Name
      , Gender
FROM    @Test;

Answer 6

You could perform this update using a case expression.

UPDATE names_table
   SET names_table.gender = ( CASE
                                  WHEN names_table.gender = 'M'
                                    THEN 'W'
                                  ELSE
                                      names_table.gender = 'M'
                              END)

I'd suggest running your update statement within a transaction and adding a simple query such as:

SELECT n.gender, *
FROM names_table

in order to check the results you'll be getting. Performing the transaction with a rollback and switching it to a commit when your results line up with what you're expecting.