Handouts on the method of two columns of data in the mysql interchange table

The following content mainly brings you two columns of data method handouts in the mysql exchange table. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

1. Create tables and records to test 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 (5Power2) unsigned NOT NULL COMMENT 'original price', `price` decimal (5jue 2) unsigned NOT NULL COMMENT 'current price', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `product` (`id`, `name`, `original_ price`, `price`) VALUES (NULL, 'ice cream', '5ice cream,' 3.5'), (NULL, 'fresh flowers', '1800,' 15'), (NULL, 'dessert', '25bread,' 12.5'), (NULL, 'toys', '55bread,' 45'), (NULL, 'wallet', '285bread,' 195'); mysql > select * from product +-+ | id | name | original_price | price | +-+ | 1 | Ice cream | 5.00 | 3.50 | | 2 | Flowers | | 4 | Toys | 55.00 | 45.00 | | 5 | wallet | 285.00 | 195.00 | +-+ 5 rows in set (0.00 sec) 2. Interchange the values of original_price and price

Beginners may swap using the following methods

Update product set original_price=price,price=original_price

However, the result of this execution will only make both the values of original_price and price be the values of price, because update has a sequence of

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 | | 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: 0mysql > select * from product +-+ | id | name | original_price | price | +-+ | 1 | Ice cream | 3.50 | 3.50 | | 2 | Flowers | | 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 method of exchange 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 | | 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.01sec) Rows matched: 5 Changed: 5 Warnings: 0mysql > select * from product +-+ | id | name | original_price | price | +-+ | 1 | Ice cream | 3.50 | 5.00 | | 2 | Flowers | | 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) |

This article explains the method of two columns of data in mysql interchange table. For more information, please pay attention to php' Chinese net.

Related recommendations:

How to generate 0,1 Random Decimal number by php

Instructions on the use of the mysql timestamp formatting function from_unixtime

Notes on the use of mysql functions concat and group_concat

These are the details of the two-column data method in the mysql interchange table. Please pay more attention to other related articles!