Parse the use of INSERT INTO SELECT in MySQL

  • 2020-05-27 07:21:05
  • OfStack

1. Introduction to grammar
There are three tables a, b, c, and now you need to insert the values of several fields from table b and table c into the corresponding fields in table a. For this case, you can use the following statement:
INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name

The above statement is more suitable for data interleaved between two tables, if more than one table is not suitable. For multiple tables, you can first set up the fields JOIN that need 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 a field of table b and f2 is a field of table c. Through JOIN query, the fields from table b and table c are combined, and then inserted into table a through SELECT nested query. Thus, this scenario is satisfied.

2. Notice grammatical errors
Note that the nested query section must have a set table alias at the end of 1, as follows:


That is, the last AS tb is required (the name tb is optional), that is, specifying a single name. Each derived new table must specify an alias, or the following error will be reported in mysql:

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

In addition, INSERT INTO SELECT cannot be added to VALUES in MySQL, that is, it cannot be written as follows:

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

Otherwise, an error is also reported: You have an error in your SQL syntax

Related articles: