How to use insert and select nesting in mysql _ MySQL

How to use insert and select nesting in mysql

This article describes how to use insert and select nesting in mysql, which is of some reference value to MySQL beginners.

Here, you need to combine fields from multiple tables in mysql and insert them into a new table. you can use an SQL statement to implement this function. The specific situation is: there are three tables a, B, and c. now we need to query the values of several fields from Table B and Table c and insert them into the corresponding fields in Table. In this case, we can use the following statement:

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

Of course, the preceding statement is suitable for data insertion between two tables. if multiple tables are not used. For multiple tables, we can join the fields to be queried and form a view before selecting 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 the field of Table B, f2 is the field of table c, and fields from Table B and Table c are combined through join queries, then insert the data to table a through the select nested query. this satisfies our needs. if more than two tables are required, fields can be combined in the form of multiple joins. Note that the table alias must be set at the end of the nested query section, as shown below:

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

That is, the final as tb is required (of course, the tb name can be retrieved at will), that is, to 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, the alias must be specified for each derived new table.