Use of the insert into select statement in the MySQL database

This article mainly introducesMySQL databaseStatement: the use of insert into select and Its syntax. Next we will introduce it through an example. We hope that this example will help you better understand the syntax of this statement, avoid unnecessary errors in future use. Now let's get started.

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 query, and then SELECTNested QueryInsert to Table a to meet this scenario. If more than two tables are required, you can combine fields 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 name of the final AS tb is a required tb. 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, an error is reported:

 
 

  
  
You have an error in your SQL syntax 
 
 

The syntax of the MySQL database is introduced here. If you want to learn more about the MySQL database, you can refer to the article here: http://database.51cto.com/mysql/, which will bring you a good harvest!