|
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)
登录后复制 |
