Shulou(Shulou.com)06/01 Report--

This paper gives an example of how mysql can query data and update to another table according to conditions. Share with you for your reference, the details are as follows:

The original database has three tables.

Travel_way: tourist itinerary table, details of storing itinerary traveltag: itinerary label table, storage route destination and other information tagrelation: label correspondence table, correspondence between storage route and destination

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

First of all, the corresponding destinations of all lines are obtained, grouped by line ID, and merged into one line, separated by commas.

The copy code is as follows: 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 checked data is stored in a new table mid.

The copy code is as follows: 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 an update, you cannot use the insert into select from statement.

The copy code is as follows: update travel_way,mid set travel_way.destination = mid.destination where travel_way.id = mid.travelway_id

Successfully imported the destination into the travel_way table as a comma-separated string

Talk about some of the methods used, group_concat

Group_concat ([DISTINCT] the field to be connected [Order BY sort field ASC/DESC] [Separator 'delimiter']), which combines the same lines

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, the values of the price field are printed on the same line, separated by commas (default)

Select id, group_concat (price) from goods group by id;+-+-+ | id | group_concat (price) | +-+-+ | 1 | 10 sec 20 | 2 | 20 | 3 | 200500 | +-+-+ 3 sec)

Group the price fields in id and reprint them on a line separated by commas.

Select id,group_concat (distinct price) from goods group by id +-+-+ | id | group_concat (distinct price) | +-+-+ | 1 | 10 Personals 20 | | 2 | 20 | | 3 | 200500 | +-+- -+ 3 rows in set (0.00 sec)

Group the values in id, print the values of the price field on a single line, separated by commas, and arrange them in reverse price order.

Select id,group_concat (price order by price desc) from goods group by id +-+-+ | id | group_concat (price order by price desc) | +-+-+ | 1 | 20Person20Person10 | | 2 | 20 | | 3 | 500200 | +- +-+ 3 rows in set (0.00 sec)

Insert into select from inserts the queried record into a table

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

Requires that 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 Dahe | 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 affectedselect * from insert_one +-+ | id | name | age | sex | +-+ | 1 | Tian Xiaosi | 25 | | 2 | Liu Daniu | 26 | | 3 | Zheng Da Hammer | 28 | | 4 | Hu Ergou | 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 only applies to import the data into an empty table, so in the actual need above, I set up a new table mid and use update to transfer and update the data.

The copy code is as follows: UPDATE tb1,tb2 SET tb1.address=tb2.address WHERE tb1.name=tb2.name

According to the matching conditions, replace (update) the data of Table 1 with the data of Table 2. Tables 1 and 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 affectedselect * from insert_one +-+ | id | name | age | sex | +-- + | 1 | Tian Xiaosi | 25 | 1 | 2 | Liu Daniu | 26 | 2 | 3 | Zheng Da Hammer | 28 | 1 | 4 | Hu Ergou | 30 | 2 | 5 | | 1 | 6 | 2 | 2 | 7 | | | 1 | | 8 | 2 | +-+ 8 rows in set |

Successfully updated the data to the sex field of the insert_one table.

More readers who are interested in MySQL-related content can check out this site's special topics: "A Summary of MySQL Common functions", "A Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.