CODEWITHSUNDEEP
mysql
shasi kanth
shasi kanth

Reputation: 7102

mysql replace values in a table

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?

Upvotes: 9

Views: 22541

Answers (5)

golimar
golimar

Reputation: 2548

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

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

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

Upvotes: 6

AgDude
AgDude

Reputation: 1185

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

Upvotes: 2

user898741
user898741

Reputation: 

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

Upvotes: 3

rabudde
rabudde

Reputation: 7722

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

Upvotes: 14

ajreal
ajreal

Reputation: 47331

Combine two queries into one :-

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

Upvotes: 3

Related Questions