Write a query to exchange values ​​between rows in a table on sql server?

Question

I mistakenly inserted the wrong data in the rows of the table, now I want to change the data. Men instead of Women and vice versa.

Below are the correct data that I expect -

Answer 1

A simple update works:

UPDATE myTable SET col1 = CASE WHEN col1 = 'male' THEN 'female' ELSE 'male' END, col2 = CASE WHEN col2 = 'female' THEN 'male' ELSE 'female' END 

Result: string values ​​will be replaced.

Hope this works for you.

Answer 2

You can use:

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

LiveDemo

Note that values ​​other than male/female , such as N/A or NULL , will remain unchanged.

---

If you want to do this only for specific names, use:

 UPDATE table_name SET Gender = CASE WHEN Name IN ('Geetha', 'Radha') THEN 'Female' WHEN Name IN ('Ram', 'Syam', 'Ravi') THEN 'Male' END WHERE Name IN ('Ram', 'Syam', 'Geetha', 'Radha', 'Ravi');