This article describes the nested use of insert and select in mysql.For those who are new to MySQL, there are Certain reference value.

Here needs to be implemented in mysql to combine fields from multiple tables and insert them into a new table, through A SQL statement fulfills this functional requirement.The specific situation is: there are three tables a, b, and c.Now you need to check the values ​​of several fields from table b and table c and insert them into the corresponding fields in table a.In this case, we can use the following statement to achieve:

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

Of course, the above statement is more suitable for inserting the data of two tables.If there are multiple tables, it will not be suitable.For multiple tables, we can join the fields that need to be queried first, then form a view and then select from.The sql code is as follows:

INSERT INTO a(field1,field2) SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb

F1 is a field of table b, and f2 is a field of table c.The fields from table b and table c are combined through a join query, and then inserted into table a through a select nested query, so This is enough for our scenario.If you need more than 2 tables, you can combine fields in the form of multiple joins.It should be noted that the table alias must be set at the end of the nested query part, as follows:

SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb

That is, the last as tb is necessary (of course, the name tb can be taken at will), that is, specify an alias, otherwise the following error will be reported in mysql:

ERROR 1248 (42000): Every derived TABLE must have its own alias

That is, each new derived table must specify an alias.