Swap Salary is a tricky question but we have an explanation for this and we will be going to solve this in very easy steps.


We are going to swap all the sex from 'm' to 'f' and 'f' to 'm' from the table salary using only the update statements without the use of any select statement.


Let's have a SQL Schema of the table:


CREATE TABLE If Not Exists salary (id int, name varchar(100), sex char(1), salary int)

INSERT INTO salary (id, name, sex, salary) VALUES ('1', 'A', 'm', '2500')

INSERT INTO salary (id, name, sex, salary) VALUES ('2', 'B', 'f', '1500')

INSERT INTO salary (id, name, sex, salary) VALUES ('3', 'C', 'm', '5500')

INSERT INTO salary (id, name, sex, salary) VALUES ('4', 'D', 'f', '500');


This will generate a table named salary containing data:


id name sex salary
1 A m 2500
2 B f 1500
3 C m 5500
4 D f 500


 Output required for the query will be:


id name sex salary
1 A f 2500
2 B m 1500
3 C f 5500
4 D m 500


- 'm' changed to 'f'
- 'f' changed to 'm'


Let's write a query, we will be going to use conditional statements to get the perfect query to solve the above question:

UPDATE salary SET sex = CASE WHEN sex = 'm' THEN 'f' WHEN sex = 'f' THEN 'm' END;


We used the CASE statement to update 'm' to 'f' and 'f' to 'm'.