MySQL interchange table in two columns of data

During the development process, it is sometimes necessary to exchange two columns of data in a table due to business requirements.

Solution Solutions

With the update command, this is entirely thanks to the powerful support of the MySQL SQL command feature.

The original data in the table looks like this:

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 |

Now it is required to swap the values of Original_price and price using the following method:

update product as a, product as b set a.original_price=b.price, a.price=b.original_price wherea.id=b.id;

Verify that:

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 |         Wallets | 195.00 | 285.00 |

Scaling issues

The above example of using update to Exchange two columns (naturally the same data type) in a table has a special case, which is actually the real problem I'm trying to solve. Specifically, some of the data that our users have initially prepared is reversed for the two columns of data in the table. However, the data entered later is not reversed. In this case, we need to find out the range of records that were previously reversed, and then implement the above actions 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. Each matching row is updated once, even if it matches the onditions multiple times. for multiple-table syntax, the ORDER by and LIMIT cannot is used.

I tried it, and I did.

Helpless we can only use the workaround. One way to do this is to first select the records of the specified range above and generate them 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 here's a little episode: MySQL does not directly support statements like the MS SQL Server select INTO newtable. However, we can use the alternative statement implementation, as follows:

Createtable tmp(Select * frommv_person2 limit 0,10);

The meaning of the above statement is that a new table of TMP is generated, and the fields and values in that table are the data from 1th through 10th in the other table Mv_person2. This enables the purpose of generating a temporary table above.

With the above temp table tmp, it is easy to use the UPDATE statement to implement the two columns of the specified range in the interchange table Mv_person2 (for example, record data from 1th to 10th). The answers are as follows:

updatemv_person2 as a, tmp as b set a.original_price=b.price, a.price=b.original_price wherea.id=b.id;

This indirectly implements the purpose of exchanging two columns of data in a specified range within a table.

MySQL interchange table in two columns of data