Swap values of two columns in MySQL?-Mysql Tutorial-php.cn

Swap values of two columns in MySQL?

PHPz

Release： 2023-08-22 09:49:02

forward

1500 people have browsed it

Swap values of two columns 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;

Copy after login

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)

Copy after login

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

Copy after login

To check the column values before swapping.

mysql> select *from SwappingTwoColumnsValueDemo;

Copy after login

The following is the output.

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

Copy after login

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

Copy after login

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

mysql> select *from SwappingTwoColumnsValueDemo;

Copy after login

The following is the output.

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

Copy after login

The above is the detailed content of Swap values of two columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!

Swap values ​​of two columns in MySQL?

Swap values of two columns in MySQL?