Shulou(Shulou.com)05/31 Report--

This article mainly introduces "mysql how to exchange two columns of data in the table". In the daily operation, I believe that many people have doubts about how to exchange two columns of data in the mysql table. The editor consulted all kinds of information and sorted out a simple and useful method of operation. I hope it will be helpful for you to answer the doubt of "how to exchange two columns of data in the mysql table". Next, please follow the editor to study!

Solution

Using the update command, this is entirely due to the strong support of the MySQL SQL command feature.

The original data in the table is similar to the following:

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 |

Now it is required to exchange the values of original_price and price, using the following methods:

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

Verify:

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 |

Extension problem

The above example of directly using update to exchange two columns in a table (naturally of the same data type) has a special case, which is actually the real problem I'm trying to solve. Specifically, our users reversed the two columns of data in the table in some of the data they initially prepared. However, there is no reverse for the data entered later. In this case, we need to find out the scope of the previous record, and then implement the above action for this part of the record. Unfortunately, the update statement in MySQL does not support the limit clause. The official formal description is as follows:

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the onditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used.

I tried it, and it was true.

But we can only use flexible methods. One method is to first select and generate the records of the above specified range into a temporary table, and then use the update statement for the original table and the temporary table to exchange the values of the corresponding fields in the above range records.

But there is an interlude here: MySQL does not directly support statements like MS SQL Server's select into newtable. However, we can do this using other flexible statements, as follows:

Create table tmp (Select * from mv_person2 limit 0pl 10)

The meaning of the above statement is to generate a new table tmp whose fields and values are data recorded from articles 1 to 10 in another table mv_person2. This achieves the above goal of generating a temporary table.

With the temporary table tmp above, it's easy to use the update statement to swap the specified range of columns in the table mv_person2 (for example, recording data from items 1 to 10). The answers are as follows:

Update mv_person2 as a, tmp as b set a.original_price=b.price, a.price=b.original_price where a.id=b.id

At this point, the study on "how to exchange two columns of data in the mysql table" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.