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)

• Related Questions & Answers
• How to update a MySQL table by swapping two column values?
• Match column values on the basis of the other two column values in MySQL
• Select a specific value between two column values in MySQL?
• Concatenate two values from the same column with different conditions in MySQL
• How to concatenate two column values into a single column with MySQL. The resultant column values should be separated by hyphen
• Shuffling column values with MySQL?
• MySQL query to get first two highest column values from a table?
• MySQL query to match any of the two strings from column values
• Multiply values of two columns and display it a new column in MySQL?
• Get all the records with two different values in another column with MySQL
• Concatenate two columns when one of such column values is null in MySQL
• MySQL randomly select 2 values from column values?
• Set ENUM in MySQL for column values
• Sum the values in a column in MySQL?
• Setting column values as column names in the MySQL query result?