A simple example of two-column data method in MySQL Implementation interchange table _mysql

Because of the recent project, there is such a demand, is the database of two data interchange, after a long time to finish, here to write a simple example, made a record.

1. Create tables and records for testing

CREATE TABLE ' product ' (
 ' id ' int ' unsigned NOT NULL auto_increment COMMENT ' products ID ',
 ' name ' varchar () NOT NULL COMMENT ' Product name ',
 ' Original_price ' decimal (5,2) unsigned not NULL COMMENT ' original price ', ' Prices '
 decimal (5,2) unsigned not N ull COMMENT ' current 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 ') , ' (NULL, 
' dessert ', ' ' 12.5 '), 
(null, ' Toys ', ', ', ', '), 
(null, ' wallet ', ' 285 ', ' 195 ');

Mysql> SELECT * from Product;
+----+--------+----------------+--------+
| id | name  | original_price |
+----+--------+----------------+--------+
| 1 |      5.00 |  3.50 |
| 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. Exchange Original_price and price values

Beginners may use the following methods to swap

Update product set Original_price=price,price=original_price;

But the result of this execution is that the value of Original_price and price will be the value of price, because update is in order,

The original_price=price is executed first, and the value of Original_price has been updated to price.

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

Execution results:

 mysql> SELECT * FROM product +----+--------+----------------+--------+ | id | name | Original_price |
Price | +----+--------+----------------+--------+
| 1 |      Ice cream |  5.00 | 3.50 | | 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,pric
E=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 | 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 results:

Mysql> SELECT * from Product;
+----+--------+----------------+--------+
| id | name   | original_price | price  |
+----+--- -----+----------------+--------+
|  1 | ice cream    |            5.00 |   3.50 |
|  2 | Flowers    |          18.00 |  15.00 |
|  3 | Desserts    |          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 | 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, I hope to help you, thank you for your support for this site!