mysql implements a sample method for querying data and updating it to another table based on conditions
- 2021-11-29 16:45:10
- OfStack
This article illustrates how mysql can query data and update it to another table according to the condition. Share it for your reference, as follows:
The original database had three tables
travel_way: Tourist route list, which stores the specific information of the route traveltag: Line label list, which stores information such as line destination tagrelation: Label correspondence table, which stores the correspondence between routes and destinationsBecause of the change in business logic, we will now merge them into a single table, inserting the destination information from traveltag into travel_way.
First, the destinations corresponding to all lines are obtained, grouped by line ID, and the destinations are merged into 1 line, separated by commas.
SELECT travel_way.id,GROUP_CONCAT(traveltag.content) FROM travel_way LEFT JOIN tagrelation on travel_way.id = tagrelation.travel_id LEFT JOIN traveltag ON tagrelation.tag_id = traveltag.id GROUP BY travel_way.id
First, the found data is stored in a newly created table mid
INSERT into mid (travelway_id,destination) SELECT travel_way.id,GROUP_CONCAT(traveltag.content) FROM travel_way LEFT JOIN tagrelation on travel_way.id = tagrelation.travel_id LEFT JOIN traveltag ON tagrelation.tag_id = traveltag.id GROUP BY travel_way.id
Then update the data of the mid table to travel_way, because it is updated, so the insert into select from statement cannot be used
update travel_way,mid set travel_way.destination = mid.destination where travel_way.id = mid.travelway_id
Successfully imported destinations as comma-delimited strings into the travel_way table
Say several methods used in 1, group_concat
group_concat (the field [Order BY] to join [Order BY sort field ASC/DESC] [Separator 'Delimiter']), which 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)
Grouped in id, 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)
Grouped in id, reprint price fields on 1 line, 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)
Grouped in id, print the values of the price field on 1 line, separated by commas, in reverse order according to 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 the queried record into a table,
INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name
Requirements target db2 must exist, the following test 1, there are two tables, the structure is as follows
select * from insert_one;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Tian Xiaosi | 25 | |
| 2 | Liu Daniu | 26 | |
| 3 | Zheng sledgehammer | 28 | |
| 4 | Hu 2 Dog | 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 sledgehammer | 28 | |
| 4 | Hu 2 Dog | 30 | |
| 5 | | | 1 |
| 6 | | | 2 |
| 7 | | | 1 |
| 8 | | | 2 |
+----+--------+-----+-----+
8 rows in set
The result is awkward. I want to update the sex field of this table instead of inserting new data, so this command is only applicable to importing data into an empty table, so in the above practical needs, I set up a new table mid and use update to transit and update the data
UPDATE tb1,tb2 SET tb1.address=tb2.address WHERE tb1.name=tb2.name
Replace (update) the data in Table 1 with the data in Table 2 according to the matching criteria. Table 1 and Table 2 must be related to each other
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 sledgehammer | 28 | 1 |
| 4 | Hu 2 Dog | 30 | 2 |
| 5 | | | 1 |
| 6 | | | 2 |
| 7 | | | 1 |
| 8 | | | 2 |
+----+--------+-----+-----+
8 rows in set
The data was successfully updated to the sex field of the insert_one table.
More readers interested in MySQL can check out the topics on this site: "MySQL Common Functions Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"
I hope this article is helpful to everyone's MySQL database.