How to Insert Multiple Rows from Subqueries into Tables in MySQL?

Inserting Multiple Rows from Subqueries into Tables

When attempting to insert multiple rows into a table using a subquery that returns more than one row, you may encounter the error 1242 (21000): Subquery returns more than 1 row. This error arises due to MySQL's constraint against inserting multiple rows using a single INSERT statement.

To resolve this issue, employ the following technique:

Combine the static row data that you wish to insert with the subquery. For instance, if you wish to insert the name "Henry" into a column named "names" for each row returned by the subquery, modify the query as follows:

INSERT INTO Results (People, names)
SELECT d.id, 'Henry'
FROM Names f
JOIN People d ON d.id = f.id

In this modified query, the static value 'Henry' is combined with the subquery. As a result, each row returned by the subquery will have the "names" column populated with the value 'Henry'.

The above is the detailed content of How to Insert Multiple Rows from Subqueries into Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!