Mysql implements a simple example of two data columns in the exchange table and two mysql Columns

Mysql implements a simple example of two data columns in the exchange table and two mysql Columns

Because of the recent project, there is a need to swap the two columns of data in the database. It takes a long time to complete the problem. Here we write a simple instance and record it.

1. Create tables and records for testing

Create table 'product' ('id' int (10) unsigned not null AUTO_INCREMENT COMMENT 'product id', 'name' varchar (50) not null comment 'product name ', 'original _ price' decimal (5, 2) unsigned not null comment 'old price', 'price' decimal (5, 2) unsigned not null comment 'spot price', primary key ('id ')) ENGINE = InnoDB default charset = utf8; insert into 'product' ('id', 'name', 'original _ price', 'price') VALUES (NULL, 'Ice cream ', '5', '3. 5 '), (NULL, 'Flowers', '18', '15'), (NULL, 'dessert ', '25', '12. 5 '), (NULL, 'toy', '55', '45'), (NULL, 'wallet', '123', '123 ');

Mysql> select * from product; + ---- + -------- + ---------------- + -------- + | id | name | original_price | price | + ---- + -------- + ---------------- + -------- + | 1 | ice cream | 5.00 | 2 | flowers | 18.00 | 15.00 | 3 | dessert | 25.00 | 12.50 | 4 | toys | 55.00 | 45.00 | 5 | wallet | 285.00 | 195.00 | + ---- + -------- + -------------- + -------- + 5 rows in set (0.00 sec)

2. Swap original_price and price values

New users may use the following methods for interchange

Update product set original_price = price, price = original_price;

However, the result of this execution will only make the values of original_price and price both price values of the price, because the update has an order,

Run original_price = price first. The value of original_price has been updated to price,

Then run price = original_price, which is equivalent to no update.

Execution result:

Mysql> select * from product; + ---- + -------- + ---------------- + -------- + | id | name | original_price | price | + ---- + -------- + ---------------- + -------- + | 1 | ice cream | 5.00 | 2 | flowers | 18.00 | 15.00 | 3 | dessert | 25.00 | 12.50 | 4 | toys | 55.00 | 45.00 | 5 | wallet | 285.00 | 195.00 | + ---- + -------- + -------------- + -------- + 5 rows in set (0.00 sec) mysql> update product set original_price = price, price = original_price; Query OK, 5 rows affected (0.00 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from product; + ---- + -------- + ---------------- + -------- + | id | name | original_price | price | + ---- + -------- + ---------------- + -------- + | 1 | ice cream | 3.50 | 2 | flowers | 15.00 | 15.00 | 3 | dessert | 12.50 | 12.50 | 4 | toys | 45.00 | 45.00 | 5 | wallet | 195.00 | 195.00 | + ---- + -------- + -------------- + -------- + 5 rows in set (0.00 sec)

The correct interchange method is as follows:

Update product as a, product as B set a. original_price = B. price, a. price = B. original_price where a. id = B. id;

Execution result:

Mysql> select * from product;
+ ---- + -------- + ---------------- + -------- +
| Id | name | original_price | price |
+ ---- + -------- + ---------------- + -------- +
| 1 | ice cream | 5.00 | 3.50 |
| 2 | flowers | 18.00 | 1, 15.00 |
| 3 | dessert | 25.00 | 12.50 |
| 4 | toys | 55.00 | 45.00 |
| 5 | wallet | 285.00 | 195.00 |
+ ---- + -------- + ---------------- + -------- +
5 rows in set (0.00 sec)

Mysql> update product as a, product as B set a. original_price = B. price, a. price = B. original_price where a. id = B. id;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0

Mysql> select * from product;
+ ---- + -------- + ---------------- + -------- +
| Id | name | original_price | price |
+ ---- + -------- + ---------------- + -------- +
| 1 | ice cream | 3.50 | 5.00 |
| 2 | flowers | 15.00 | 1, 18.00 |
| 3 | dessert | 12.50 | 25.00 |
| 4 | toys | 45.00 | 55.00 |
| 5 | wallet | 195.00 | 285.00 |
+ ---- + -------- + ---------------- + -------- +
5 rows in set (0.00 sec)

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!