How to Insert Multiple Rows from a Subquery into a MySQL Table When the Subquery Returns More Than One Row?

Inserting Multiple Rows from a Subquery into a MySQL Table

To insert multiple rows into a MySQL table from a subquery, it's crucial to ensure that the subquery returns only a single row. In the provided code snippet, the insertion query:

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

attempts to insert two values into the People and names columns. However, the subquery inside the first parenthesis returns multiple rows for d.id. This leads to the error "Subquery returns more than 1 row."

Solution with Combined Static String

To insert the static string "Henry" while preserving the results from 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

By combining the static string "Henry" with the subquery using a comma, the query explicitly sets the value for the names column to "Henry" for each row returned by the subquery. This ensures that the query inserts the results of the subquery along with the specified value into the table without encountering the error.

The above is the detailed content of How to Insert Multiple Rows from a Subquery into a MySQL Table When the Subquery Returns More Than One Row?. For more information, please follow other related articles on the PHP Chinese website!