SQL Server Gender Swap Query

Question

I have a Table having the following column:

ID, Name, Designation, Salary, Contact, Address, Gender

Accidentally for all male Gender i have entered 'Female' and similarly for all Female gender i have entered 'Male'. For Exmaple

0023 Scott Developer 15000 4569865 Cliff_Eddington,USA Female

I the above line There should be Male instead of female. And there are all Male whose gender has been updated as Female. Same case for all Female Gender has been updated as Male.

Is there any Single query through which i can change all the Rows whose Gender is Male Change it to Female and All the Rows whose Gender is Female Change it to Male.

Answer 1

this should do it

UPDATE tbl SET Gender = (CASE WHEN Gender = 'Male' THEN 'Female' ELSE 'Male' END)

(sorry, was mysql syntax, fixed for ms-sql)

Answer 2

I would run a SELECT COUNT(*) before running the updates so that you get the correct results and then COMMIT the transaction:

BEGIN TRANSACTION

UPDATE tableA 
   SET Gender = CASE  
                WHEN Gender = 'Male' THEN 'Female'  
                ELSE 'Male'  
                END

--COMMIT TRANSACTION
--ROLLBACK TRANSACTION