Shulou(Shulou.com)05/31 Report--

This article introduces how to query data in MySQL and update to another table according to the conditions. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Data case

The original database has three tables.

T_user: user table, which stores the basic information of the user.

T_role: role table, which stores role information.

T_role_user: store the corresponding relationship between the role and the user.

Because of the change in business logic, it is time to merge them into one table and insert the role information from t_role into the t_user.

First, get the corresponding roles of all users, group them by user ID, merge the roles into one line, separated by commas.

SELECT tweak user.id.GROUPPROCONcat (t_role.content) FROM t_user LEFT JOIN t_role_user ont _ user.id = t_role_user.t_user_id LEFT JOIN t_role ON t_role_user.t_role_id = t_role.id GROUP BY t_user.id

First, the checked data is stored in a new table mid.

INSERT into mid (tweak username _ instruction _ info) SELECT _

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

Update tweak username mid set t_user.t_role_info = mid.t_role_info where t_user.id = mid.t_user_id

Successfully imported the destination into the t_user 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 | 2 | 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 rows in set (0.00 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 | 20Person20 10 | | 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 | Glacier 001 | 25 | | 2 | Glacier 002 | 26 | | 3 | Glacier 003 | 28 | | 4 | Glacier 004 | 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 Dahe | 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.

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 affected select * from insert_one +-+ | id | name | age | sex | +-+ | 1 | Glacier 001 | 25 | 1 | 2 | 2 | Glacier 002 | 26 | 2 | 3 | Glacier 003 | 28 | 1 | 4 | Glacier 004 | 30 | 2 | 5 | | 1 | 1 | 6 | | | 2 | | 7 | | 1 | | 8 | 2 | +-+ 8 rows in set |

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

On how to query the data in MySQL and update to another table according to the conditions to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.