How to Swap a Values in MySQL?

The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using the UPDATE statements as per our requirement. Suppose we want to need to write a SQL query to swap all ‘a’ and ‘m’ values (i.e., change all ‘f’ values to ‘m’ and vice versa) with a single update statement and no intermediate temporary tables, then following are the steps:

Step 1: Creating the Database

Use the below SQL statement to create a database called geeks:

Query:

create database geeksforgeeks;

Step 2: Using the Database

Use the below SQL statement to switch the database context to geeks:

Query:

use geeksforgeeks;

Step 3: Table Definition

We have the following Salary table in our geeksforgeeks database.

Query:

create table Salary(id int  , name varchar(20) , sex varchar(1) ,
 salary int);

the table Salary contains information about an employee.

Step 4: Inserting Values in the table

Query:

insert into Salary values(1 , "A" , "m" , 2500);
insert into Salary values(2 , "B" , "f" , 1500);
insert into Salary values(3 , "C" , "m" , 5500);
insert into Salary values(4 , "D" , "f" ,  500);

Step 5: Suppose we want to update a particular value in the table then the query will be as follows

MySQL Query:

update Salary set sex = if(sex='m' , 'f','m');

here in the above query, we are using the IF() function to swap f and m, return f if sex is m, return m otherwise.

Output: