如何在SQL Server 2005中同时将m更新为f和将f更新为m [英] how to update m to f and f to m at the same time in sql server 2005

问题描述

假设我有一个表，其结构为

Hi,

Suppose I have a table structured as

name     |   gender
 anthony       m
 micheal       m
 jane          f
 kim           f

现在假设我想将m更改为f，同时将f更改为m
我是这样我想同时将m替换为f和f替换为m.我该怎么办?

在执行sql查询后，我应该获得的表结构应如下所示:

Now suppose I want to change m to f and at the same time f to m how can
I do so. I want to replace m to f and f to m simultaneously. How can i do so?

The table structure I should get after executing the sql query should be like this after

name     |   gender
 anthony       f
 micheal       f
 jane          m
 kim           m

请帮忙.

Please help.

推荐答案

<br />
<pre lang="sql"><br />
UPDATE [MyTable]<br />
SET GENDER = CASE<br />
                WHEN GENDER=''M'' THEN ''F''<br />
                WHEN GENDER=''F'' THEN ''M''<br />
                ELSE GENDER<br />
             END</pre><br />

如果您指定了"性别"字段.

如果您使用的是位(假设0 =男性& 1 =女性或1 =男性& 0 =女性)
更简单-您可以使用此语句

更新Your_Table_Name SET性别=〜性别

如果您使用的是字符串或任何其他数据类型，则可以分多个步骤进行操作(在这里，我假设字符串"m"代表男性，而"f"代表女性)

步骤1-暂时将"m"更改为其他值(例如"t")
更新Your_Table SET Gender =``t''其中Gender =``m''

步骤2-将"f"更改为"m"
更新Your_Table SET Gender =``m''其中Gender =``f''

步骤3-将临时重命名的"m"("t")更改为"f"
更新Your_Table SET Gender ="f"，其中Gender ="t"

It''d be helpful if you had specified the data type of your ''Gender'' field.

If you''re using a Bit (assuming 0=male & 1=female OR 1=male & 0=female)
it''s simpler - you can use this statement

Update Your_Table_Name SET Gender = ~ Gender

If you''re using a string or any other data type, you can do it in multiple steps (I''ll assume string here ''m'' for male & ''f'' for female)

STEP 1 - Temporarily change the ''m'' to some other value (say ''t'')
Update Your_Table SET Gender = ''t'' where Gender = ''m''

STEP 2 - Change ''f'' to ''m''
Update Your_Table SET Gender = ''m'' where Gender = ''f''

STEP 3 - Change the temporarily renamed ''m'' (''t'') to ''f''
Update Your_Table SET Gender = ''f'' where Gender = ''t''

Costica U，非常感谢.您也提供了我的解决方案.我从上个星期开始尝试...再次非常感谢.

Costica U, thanks a lot. You have given my solution also. I was trying it from last one week... Again Thanks a lot.

这篇关于如何在SQL Server 2005中同时将m更新为f和将f更新为m的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！