Shulou(Shulou.com)05/31 Report--

What are the knowledge points of this article "INSERTINTOSELECT grammar errors?" most people do not understand, so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "INSERTINTOSELECT grammar error precautions" article.

What is the INSERTINTOSELECT statement?

There are three tables a, b, c, and now you need to check the values of several fields from table b and table c and insert them into the corresponding fields in table A. In this case, you can use the following statement:

INSERTINTOdb1_name (field1,field2) SELECTfield1,field2FROMdb2_name

The above statement is more suitable for inserting data between two tables, but it is not suitable for multiple tables. For multiple tables, you can first JOIN the fields that need to be queried, and then form a view and then SELECTFROM:

INSERTINTOa (field1,field2) SELECT*FROM (SELECTb.f1,c.f2FROMbJOINc) AStb

F1 is the field of table b and f2 is the field of table c. The fields from table b and table c are combined by JOIN query, and then inserted into table a by SELECT nested query, which satisfies this scenario. If you need more than 2 tables, you can combine fields in the form of multiple JOIN.

What are the considerations for INSERTINTOSELECT syntax errors?

It is important to note that the nested query section must end with a set table alias, as follows:

SELECT*FROM (SELECTf1,f2FROMbJOINc) AStb

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

ERROR1248 (42000): EveryderivedTABLEmusthaveitsownalias

In addition, INSERTINTOSELECT cannot be added with VALUES in MySQL, that is, it cannot be written as follows:

INSERTINTOdb1_name (field1,field2) VALUESSELECTfield1,field2FROMdb2_name

Otherwise, an error will be reported:

YouhaveanerrorinyourSQLsyntax

The above is about the content of this article on "what are the notices for grammatical errors in INSERTINTOSELECT?" I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.