Swapping two column values in MySQL?

To swap two columns, we can apply the below swapping logic.

• Add both values and store them into the first column

• Subtract the first column's value from the second and store it into the second column.

• Subtract the first column's value from the updated second column and store it into the first.

The above rule structure is as follows. Suppose, the first column is a and the second column is b.

 1. a = a+b; 2. b = a-b; 3. a = a-b; 

Now we will apply the above rule in order to swap the two column values.

Creating a table.

 mysql> create table SwappingTwoColumnsValueDemo   -> (   -> FirstColumnValue int,   -> SecondColumnValue int   -> ); Query OK, 0 rows affected (0.49 sec) 

Inserting some records.

 mysql>  insert into SwappingTwoColumnsValueDemo values(10,20),(30,40),(50,60),(70,80),(90,100); Query OK, 5 rows affected (0.19 sec) Records: 5  Duplicates: 0  Warnings: 0 

To check the column values before swapping.

 mysql> select *from SwappingTwoColumnsValueDemo; 

The following is the output.

 +------------------+-------------------+ | FirstColumnValue | SecondColumnValue | +------------------+-------------------+ |               10 |    20 | |               30 |    40 | |               50 |    60 | |               70 |    80 | |               90 |   100 | +------------------+-------------------+ 5 rows in set (0.00 sec) 

Syntax to swap column values.

 mysql> UPDATE SwappingTwoColumnsValueDemo   -> SET FirstColumnValue = FirstColumnValue+SecondColumnValue,   -> SecondColumnValue = FirstColumnValue-SecondColumnValue,   -> FirstColumnValue = FirstColumnValue-SecondColumnValue; Query OK, 5 rows affected (0.15 sec) Rows matched: 5  Changed: 5 Warnings: 0 

To check if the column values have been swapped or not.

 mysql> select *from SwappingTwoColumnsValueDemo; 

The following is the output.

 +------------------+-------------------+ | FirstColumnValue | SecondColumnValue | +------------------+-------------------+ |               20 |    10 | |               40 |    30 | |               60 |    50 | |               80 |    70 | |              100 |      90 | +------------------+-------------------+ 5 rows in set (0.00 sec)