mysql替换表中的值

I have a users table in my mysql database with columns like id, age and gender. I have inserted around 500 records into it.

我的mysql数据库中有一个用户表,其中包含id,age和gender等列。我已经插入了大约500条记录。

Now i need to interchange the gender for the records, i.e., replace male with female and female with male.

现在我需要为记录交换性别,即用男性代替男性与女性和女性。

I thought to do it this way:

我想这样做:

update users set gender='female' where gender='male';
update users set gender='male' where gender='female';

But as you can see, as soon as i run the first query, all the records will be updated with the gender set to 'female'.

但正如您所看到的,只要我运行第一个查询,所有记录都将更新,性别设置为“女性”。

How can i modify the query or shall i go another way?

我该如何修改查询或者我应该采取另一种方式?

5 个解决方案

#1

update users set gender=case when gender='male' then 'female' else 'male' end where gender in ('male','female');

#2

update users set gender='tmp' where gender='male';

更新用户设置gender ='tmp',其中gender ='male';

update users set gender='male' where gender='female';

更新用户设置gender ='male',其中gender ='female';

update users set gender='female' where gender='tmp';

更新用户设置gender ='female',其中gender ='tmp';

#3

Combine two queries into one :-

将两个查询合并为一个: -

update users set gender = if (gender='female', 'male', 'female');

#4

update users set gender='fem' where gender='male';
update users set gender='male' where gender='female';
update users set gender='female' where gender='fem';

#5

update users set gender='wasmale' where gender='male';
update users set gender='male' where gender='female';
update users set gender='female' where gender='wasmale';