MySQL interchange table in two columns of data method

1. Create tables and records for testing

CREATETABLE' Product ' (' ID 'int) unsignedNotNULL auto_increment COMMENT' Product ID ',' Name 'varchar50)NotNULL COMMENT' Product name ',' Original_price 'Decimal5,2) unsignedNotNULL COMMENT' Original price ',' Price 'Decimal5,2) unsignedNotNULL COMMENT' Pay ',PRIMARYKEY (' ID ')) engine=innodbDEFAULT Charset=utf8;INSERTInto' Product ' (' ID ',' name ', ' original_price ', ' price ') VALUES (null, ' ice cream ', ' 5 ', ' 3.5 '), (null, ' flowers ') , ' + ', ' (null, ' dessert ', ' + '  , ' 12.5 '), (null, ' toys ', ' ' 45 '), ( C20>null, ' purse ', ' 285 ', ' 195 ');           

mysql> select * from product;+----+--------+----------------+--------+| id | name   | original_price | price  |+----+--------+----------------+--------+|  1 | 雪糕   |           5.00 |   3.50 ||  2 | 鲜花   |          18.00 |  15.00 ||  3 | 甜点   |          25.00 |  12.50 ||  4 | 玩具   |          55.00 |  45.00 ||  5 | 钱包   |         285.00 | 195.00 |+----+--------+----------------+--------+5 rows in set (0.00 sec)

2. Interchange the value of Original_price and price

Newbies may use the following methods to swap

update product set original_price=price,price=original_price;

• 1

However, the result of this execution will only cause the value of original_price and price to be the value of price, because the update is ordered,
Execute original_price=price First, the value of Original_price has been updated to price,
Then execute price=original_price, which is equivalent to no updates.

Execution Result:

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 |         Wallets | 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:0mysql> select * FROM product;+----+---- ----+----------------+--------+| id | name | original_price | price |+----+--------+----------------+--------+| 1 | snow Cakes | 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:

set a.original_price=b.price, a.price=b.original_price where a.id=b.id;

• 1

Execution Result:

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 |         Wallets | 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:0mysql> select * F Rom 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) 
 
 Last I use     

set a.original_price=b.price, a.price=b.original_price where a.id=b.id;

http://blog.csdn.net/fdipzone/article/details/50864196

MySQL interchange table in two columns of data method