Nesting of inserts and select in Mysql using the Resolve combination field insert problem _mysql

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.