In MySQL table update 'a' record with 'b' and 'b' with 'a'

In my table there are two fi开发者_JAVA技巧eld, one is name and other is gender. I want to fire query so that every male is update with female and viceversa.

I don't want to use procedure, trigger or function. I have to do this only with simple query.

In MSSQL you could do this:

UPDATE table SET gender = CASE WHEN gender = 'M' THEN 'F' ELSE 'M' END

If there is anything similar is My-SQL then this is one easy statement.

Make it a three step.

-- Step 1: Give the males a temporary gender value (gender X)

-- Step 2: Set the female records to male (F to M)

-- Step 3: Set the old male records to female (X to F)

Update table Set Gender = 'X' where Gender = 'M'
Update table Set Gender = 'M' where Gender = 'F'
Update table Set Gender = 'F' where Gender = 'X'