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!