An example of how to query data in MySQL and update it to another table based on conditions

This article uses an example to describe how MySQL can query data and update it to another table based on conditions. Share with you for your reference, the details are as follows:

The original database has 3 tables

• travel_way: travel route table, storing detailed information of the route
• traveltag: route tag table, storing route destination and other information
• tagrelation: tag correspondence table, storing the correspondence between routes and destinations

Because of the change in business logic, we now need to merge them into one table and insert the destination information in traveltag into travel_way.

First, get the destinations corresponding to all routes, group them by route ID, merge the destinations into one line, and separate them with commas.

First store the found data in a newly created table mid

Then update the data of the mid table to travel_way. Because it is an update, you cannot use the insert into select from statement.

Successfully imported the destinations into the travel_way table as a comma-delimited string

Let's talk about the methods used, group_concat

group_concat( [DISTINCT] Field to be connected [Order BY sort field ASC/DESC] [Separator 'separator'] ), this function can combine the same rows

select * from goods;
+------+------+
|id| price|
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)

Group by id and print the values ​​of the price field on the same line, separated by commas (default)

select id, group_concat(price) from goods group by id;
+------+--------------------+
| id| group_concat(price) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

Group by id, print the price field in one line without duplication, separated by commas

select id,group_concat(distinct price) from goods group by id;
+------+-----------------------------+
| id| group_concat(distinct price) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

Group by id, print the value of the price field in one line, separated by commas, and sorted in descending order of price

select id,group_concat(price order by price desc) from goods group by id;
+------+---------------------------------------+
| id| group_concat(price order by price desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)

insert into select from insert into select from a table.

INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name

The target db2 must exist. Let's test it. There are two tables with the following structure:

select * from insert_one;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Tian Xiaosi | 25 | |
| 2 | Liu Daniu | 26 | |
| 3 | Zheng Dachui | 28 | |
| 4 | Hu Ergou | 30 | |
+----+--------+-----+-----+
4 rows in set

 
select * from insert_sex;
+----+-----+
| id | sex |
+----+-----+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 2 |
+----+-----+
4 rows in set

Find gender data from Table 2 and insert it into Table 1

into insert_one(sex) select sex from insert_sex;
Query OK, 4 rows affected
select * from insert_one;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Tian Xiaosi | 25 | |
| 2 | Liu Daniu | 26 | |
| 3 | Zheng Dachui | 28 | |
| 4 | Hu Ergou | 30 | |
| 5 | | | 1 |
| 6 | | | 2 |
| 7 | | | 1 |
| 8 | | | 2 |
+----+--------+-----+-----+
8 rows in set

The result is embarrassing. I want to update the sex field of this table instead of inserting new data. Then this command is only applicable to importing data into an empty table. So in the actual need above, I created a new table mid and used update to transfer and update the data.

update insert_one,insert_sex set insert_one.sex = insert_sex.sex where insert_one.id = insert_sex.id;
Query OK, 4 rows affected
select * from insert_one;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Tian Xiaosi | 25 | 1 |
| 2 | Liu Daniu | 26 | 2 |
| 3 | Zheng Dachui | 28 | 1 |
| 4 | Hu Ergou | 30 | 2 |
| 5 | | | 1 |
| 6 | | | 2 |
| 7 | | | 1 |
| 8 | | | 2 |
+----+--------+-----+-----+
8 rows in set

The data is successfully updated into the sex field of the insert_one table.

Readers who are interested in more MySQL-related content can check out the following topics: "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.