Switching values in a column with one update statement

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' ); 

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');

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.

Tags:

Update