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.