mysql replace values in a table

Question

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

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?

rabudde · Accepted Answer · 2011-12-06 13:26:36Z

14

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

answered Dec 6, 2011 at 13:26

rabudde

7,7826 gold badges58 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

toastrackengima · Accepted Answer · 2020-05-07 04:57:48Z

6

UPDATE users SET gender='tmp' WHERE gender='male';

UPDATE users SET gender='male' WHERE gender='female';

UPDATE users SET gender='female' WHERE gender='tmp';

edited May 7, 2020 at 4:57

toastrackengima

9,0585 gold badges55 silver badges64 bronze badges

answered Dec 6, 2011 at 13:26

golimar

2,5881 gold badge24 silver badges37 bronze badges

1 Comment

golimar Over a year ago

This answer updates the table records multiple times, check out the other ones to do it in one go

ajreal · Accepted Answer · 2011-12-06 13:26:23Z

3

Combine two queries into one :-

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

answered Dec 6, 2011 at 13:26

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

2 Comments

rabudde Over a year ago

assuming, you have a third gender, this could update rows, which you won't to be updated ;)

rabudde Over a year ago

naturally this should be so ;) but there could be an 'undefined' state (if OP allows this case), when a user won't give any data about his gender (i.e. the field gender could be empty and your statement would manipulate this field). but +1 for using IF

user898741 · Accepted Answer · 2011-12-06 13:26:48Z

3

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

answered Dec 6, 2011 at 13:26

user898741

Comments

AgDude · Accepted Answer · 2011-12-06 13:26:56Z

2

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

answered Dec 6, 2011 at 13:26

AgDude

1,1851 gold badge12 silver badges29 bronze badges

Collectives™ on Stack Overflow

mysql replace values in a table

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related