The use of the INSERT into select from MySQL

How to combine fields from multiple tables in MySQL and then insert them into a new table, implemented by an SQL statement. The case is: there are three tables A, B, C, now you need to check the values of several fields from table B and table C into the corresponding fields in table A. In this case, we can use the following statement to implement:

INSERT into Db1_name (field1,field2) SELECT field1,field2 from Db2_name

　　

Of course, the above statement is more suitable for two tables of data interpolation, if more than one table is not adapted. For multiple tables, we can first join the fields that need to be queried, then compose a view and select from:

INSERT into a (field1,field2) SELECT * FROM (SELECT f1,f2 from B JOIN c) as TB

　　

Where F1 is the field of table B, F2 is the field of Table C, the fields from table B and table C are combined by a join query and then inserted into table A by a select nested query, which satisfies our scenario, if more than 2 tables are needed, You can combine fields in the form of multiple joins. It is important to note that the nested query section must finally have a set table alias, as follows:

SELECT * FROM (select f1,f2 from B JOIN c) as TB

　　

That is, the last as TB is a must (of course, the name of TB can be arbitrary), that is, specify an alias, or in MySQL will report the following error:

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

　　

That is, each new table you derive must specify an alias.

The use of the INSERT into select from MySQL