How to combine fields from multiple tables in MySQL and then insert them into a new table, implemented in a single SQL statement. The scenario is: There are three tables A, B, and C, and now you need to insert the values from table B and table C to find the corresponding fields in table A. For this situation, 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 multiple tables are not adapted. For multiple tables, we can join the fields that need to be queried, then make a view and then 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 a field in table C that combines the fields from table B and Table C by a join query, and then inserts it into table A through a select nested query, which satisfies our scenario, if more than 2 tables are required, You can combine fields in the form of multiple joins. It is important to note that the nested query section must finally have a table alias set, as follows:
SELECT * FROM (select f1,f2 from B JOIN c) as TB
That is, the last as TB is required (of course TB name is optional), that is, specify an alias, otherwise in MySQL will report the following error:
ERROR 1248 (42000): Every derived TABLE must have its own alias
That is, each derived new table must have an alias specified.