How to Use insert and select nesting in mysql, mysqlinsert

How to Use insert and select nesting in mysql, mysqlinsert

This article describes how to use insert and select nesting in mysql, which is of some reference value to MySQL beginners.

Here, you need to combine fields from multiple tables in mysql and insert them into a new table. You can use an SQL statement to implement this function. 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. The SQL code is as follows:

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:

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

That is, the final as tb is 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.

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'

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.