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 destinations

Because 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.


Related articles: