CODEWITHSUNDEEP
mysqlsqlsql-update
click
click

Reputation: 467

Update column data without using temporary tables

There is a table called EMP, and a column called Gender, which is defined as VARCHAR(1), so 'M' for male and 'F' for female. A developer accidentally changed 'M' to 'F' and 'F' to 'M'.

How do you correct this without using a temptable?

Upvotes: 3

Views: 950

Answers (2)

Mureinik
Mureinik

Reputation: 311338

You could use a case expression:

UPDATE emp
SET    gender = CASE gender WHEN 'M' THEN 'F' ELSE 'M' END

EDIT:
The above statement assumes, for simplicity's sake, that 'M' and 'F' are the only two options - no nulls, no unknowns, no nothing. A more robust query could eliminate this assumption and just strictly replace Ms and Fs leaving other possible values untouched:

UPDATE emp
SET    gender = CASE gender WHEN 'M' THEN 'F' 
                            WHEN 'F' THEN 'M'
                            ELSE gender
                END

Upvotes: 4

Jens
Jens

Reputation: 69440

Use these 3 update statements:

update EMP set gender='X' where gender='M';
update EMP set gender='M' where gender='F';
update EMP set gender='F' where gender='X';

Upvotes: 2

Related Questions