Mysql replaces values ​​in a table

Question

I have a user table in my mysql database with columns like id, age and gender. I included about 500 entries in it.

Now I need to change the gender for the records, i.e. replace a man with female and female men.

I thought of doing it like this:

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 launched the first request, all entries will be updated if the gender is set to "female".

How can I change the request or should I go the other way?

Answer 1

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

Answer 2

Users update gender = 'tmp', where gender = 'male';

Users update gender = 'male', where gender = 'female';

users update gender = 'woman', where gender = 'tmp';

Answer 3

Combine the two queries into one: -

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

Answer 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'; 

Answer 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';