不使用临时表更新列数据。

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'.

有一个名为EMP的表，和一个名为Gender(性别)的列，定义为VARCHAR(1)，所以男性用“M”，女性用“F”。一个开发人员不小心把“M”改成了“F”，把“F”改成了“M”。

How do you correct this without using a temptable?

你如何在不使用诱惑的情况下修正这个问题?

2 个解决方案

#1

You could use a case expression:

您可以使用case表达式:

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:

编辑:为了简单起见，上面的语句假设'M'和'F'是唯一的两个选项——没有null，没有未知数，什么都没有。一个更健壮的查询可以消除这个假设，只需要严格替换Ms和Fs，不影响其他可能的值:

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

#2

Use these 3 update statements:

使用以下3个更新语句:

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

#1