Clever Geek Handbook

Updating column data without using temporary tables

There is a table under the title EMP

and a column with a name Gender

that is defined as VARCHAR(1)

, therefore "M" for men and "F" for women. The developer accidentally changed "M" to "F" and "F" to "M".

How do you fix this without using seductive?

+3


source to share


2 answers


You can use the expression case

:

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


EDIT:
The above statement assumes for simplicity that 'M'

both 'F'

are the only two parameters - no null

s, unknown, nothing. A more robust query might eliminate this assumption and just strictly replace M

and F

leave the other possible values ​​untouched:

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


source


Use these three update operators:



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


source






More articles:


All Articles