1. Syntax Introduction
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 to the corresponding fields in table. In this case, you can use the following statement:
Insert into db1_name (field1, field2) Select field1, field2 from db2_name
The preceding statement is suitable for data insertion between two tables. For multiple tables, you can first join the fields to be queried, and then form a view, and then select from:
Insert into a (field1, field2) Select * from (select B. F1, C. 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 this scenario. If more than two tables are required, fields can be combined in the form of multiple joins.
2. syntax errors
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 (the TB name can be retrieved at will), that is, an alias is specified. An alias must be specified for each derived new table. Otherwise, the following error is reported in MySQL:
Error 1248 (42000): Every derived table must have its own Alias
In addition, insert into select in MySQL cannot be added with values, that is, it cannot be written as follows:
Insert into db1_name (field1, field2) values select field1, field2 from db2_name
Otherwise, the following error occurs: You have an error in your SQL syntax.