Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
5  
Were you asked to assume a particular database product? e.g. MySQL, SQL Server, Oracle, PostgreSQL ...? – Paul White Jan 4 at 19:40
up vote 14 down vote accepted

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' ); 
share|improve this answer

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');
share|improve this answer

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> 
share|improve this answer

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.

share|improve this answer
1  
Let's hope all the M and W were entered in upper case to avoid unexpected 7 or ` -` appearing in the results. – Martin Smith Jan 5 at 9:00
    
@MartinSmith: Very good point. If they weren't, we'll have to replace ASCII(Gender) with ASCII(UPPER(Gender)), which is less elegant, although not much. – Andriy M Jan 5 at 12:30
    
@MartinSmith if there are lower case m and w's, won't they be rejected by the WHERE clause? – ypercubeᵀᴹ Jan 5 at 15:15
    
@YperSillyCubeᵀᴹ - Only in case sensitive collations (which aren't that usual IME) – Martin Smith Jan 5 at 15:16

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;
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.