Nested use of insert and select in mysql, mysqlinsert

Source: Internet
Author: User
Tags mysql insert

Nested use of insert and select in mysql, mysqlinsert

How to combine fields from multiple tables in mysql and insert them into a new table using an SQL statement. The specific situation is: 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 into the corresponding fields in table. In this case, we can use the following statement:

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

Of course, the preceding statement is suitable for data insertion between two tables. If multiple tables are not used. For multiple tables, we can join the fields to be queried and form a view before selecting from:

INSERT INTO a(field1,field2) SELECT * FROM(SELECT f1,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 our needs. If more than two tables are required, fields can be combined in the form of multiple joins. Note that the table alias must be set at the end of the nested query section, as shown below:

1SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb

That is, the lastAs tbIs required (of course, the tb name can be retrieved at will), that is, to specify an alias, otherwise the following error will be reported in mysql:

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

That is, the alias must be specified for each derived new table.

Reprinted please indicate the source http://blog.csdn.net/yc7369
Use select in mysql insert

INSERT
Aa
(A, B, c, d, e, f, g, h)
SELECT
Id as a, title, c, d,
E, f, g, h
FROM
B
WHERE
Id = 1

In this way, batch insert means to insert qualified records of Table B to table aa.

Insert statement nested select statement

There cannot be subqueries in the VALUES clause, so you can:
Insert into VoteRecord (IP, TopicNum) select '"+ ip +"', ID from Topic where [Content] = '"+ topic + "'
The actually generated statement should be as follows:
Insert into VoteRecord (IP, TopicNum) select '192. 168.1.1 ', ID from Topic where [Content] = '20140901'
However, to ensure that no error occurs, it is best to add the TOP 1 clause or the MAX () function to the subquery to ensure that the subquery record is
Insert into VoteRecord (IP, TopicNum) select '192. 168.1.1 ', max (ID) from Topic where [Content] = '2016'

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.