SQL Server 性别交换查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3021549/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Gender Swap Query
提问by Sanju
I have a Table having the following column:
我有一个包含以下列的表：
ID, Name, Designation, Salary, Contact, Address, Gender
Accidentally for all male Gender i have entered 'Female' and similarly for all Female gender i have entered 'Male'. For Exmaple
偶然地，对于所有男性，我都输入了“女性”，同样，对于所有女性，我都输入了“男性”。例如
0023 Scott Developer 15000 4569865 Cliff_Eddington,USA Female
I the above line There should be Male instead of female. And there are all Male whose gender has been updated as Female. Same case for all Female Gender has been updated as Male.
我上面那行应该是男性而不是女性。并且所有男性的性别都已更新为女性。所有女性性别的相同案例已更新为男性。
Is there any Single query through which i can change all the Rows whose Gender is Male Change it to Female and All the Rows whose Gender is Female Change it to Male.
是否有任何单一查询，通过它我可以将所有性别为男性的行更改为女性，将性别为女性的所有行更改为男性。
回答by chris
this should do it
这应该这样做
UPDATE tbl SET Gender = (CASE WHEN Gender = 'Male' THEN 'Female' ELSE 'Male' END)
(sorry, was mysql syntax, fixed for ms-sql)
（抱歉，是 mysql 语法，已针对 ms-sql 修复）
回答by Neil Knight
I would run a SELECT COUNT(*)before running the updates so that you get the correct results and then COMMITthe transaction:
我会SELECT COUNT(*)在运行更新之前运行a以便您获得正确的结果然后COMMIT进行交易：
BEGIN TRANSACTION
UPDATE tableA
SET Gender = CASE
WHEN Gender = 'Male' THEN 'Female'
ELSE 'Male'
END
--COMMIT TRANSACTION
--ROLLBACK TRANSACTION
回答by Adriaan Stander
You can try something like
你可以尝试类似的东西
UPDATE TABLE
SET Gendeer = CASE
WHEN Gender = 'Male'
THEN 'Female'
ELSE 'Male'
END
回答by Verma007
1) First Method
1) 第一种方法
Update *Table_Name*
Set *Column_Name* = Case *Column_Name*
When 'M' then 'F'
When 'F' then 'M'
End
2) Second Method
2) 第二种方法
Update *Table_Name*
Set *Column_Name* = IF('M','F','M')
- Not Pretty much sure about the 2) but 1) works.
- 不太确定 2) 但 1) 是否有效。
回答by SidD
THIS IS THE CORRECT ONE.
这是正确的。
UPDATE dbo.EmpGender
SET Gender = (CASE
WHEN Gender = 'MALE' THEN 'FEMALE' ELSE 'MALE'
END)
IF YOU DONT WANT TO USE CASE STATEMENT THEN.
UPDATE dbo.EmpGender
SET Gender = REPLACE(('fe'+Gender),'fefe','')
IN SQL 2012 YOU CAN USE
UPDATE dbo.EmpGender
SET Gender = IIF(Gender = 'male', 'female', 'male')
回答by Sagar Maniar
UPDATE GENDER(TABLE NAME)
SET GENDER(COLUMN NAME) = CASE WHEN GENDER(COLUMNNAME)='MALE'
THEN 'FEMALE'
ELSE 'MALE'
END;
Here my table name and column name are same so it is clearly specified in brackets.
这里我的表名和列名是相同的，所以它在括号中明确指定。
回答by Kenneth Chen
A bit old thread, it should be a bit faster to use IF to swap gender compared to CASE WHEN:
有点旧的线程，与 CASE WHEN 相比，使用 IF 交换性别应该快一点：
UPDATE table
SET Gender = IF(GENDER='Male','Female','Male');
回答by Sudheer Kumar
If you are looking for a generic way of doing it, you can also use below queries. I will not say this method is faster or simpler but it would be generic to all relational databases. I came here to findout a query without DECODE and CASE statements and found this answer. So thought of sharing this.
如果您正在寻找一种通用的方法，您还可以使用以下查询。我不会说这种方法更快或更简单，但它对所有关系数据库都是通用的。我来这里是为了找出一个没有 DECODE 和 CASE 语句的查询，并找到了这个答案。所以想到了分享这个。
UPDATE tableA SET Gender = 'Temp' WHERE Gender = 'Male';
UPDATE tableA SET Gender = 'Male' WHERE Gender = 'Female';
UPDATE tableA SET Gender = 'Female' WHERE Gender = 'Temp';
UPDATE tableA SET Gender = 'Temp' WHERE Gender = 'Male';
UPDATE tableA SET Gender = 'Male' WHERE Gender = 'Female';
UPDATE tableA SET Gender = 'Female' WHERE Gender = 'Temp';
