Reputation: 7102
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
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
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
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
Reputation: 7722
update users set gender=case when gender='male' then 'female' else 'male' end where gender in ('male','female');
Upvotes: 14
Reputation: 47331
Combine two queries into one :-
update users set gender = if (gender='female', 'male', 'female');
Upvotes: 3