How to Correctly Insert Multiple Values into a Table Using a Subquery?

Inserting Values into a Table Using a Subquery

Inserting values into a table using a subquery with multiple results can be challenging. Consider the scenario where you have two tables, article and prices, and you want to insert entries into prices based on specific IDs obtained from article.

A common approach, which often leads to SQL errors, is to use a subquery in the VALUES clause:

INSERT INTO prices (group, id, price) 
VALUES (7, (select articleId from article WHERE name LIKE 'ABC%'), 1.50);

This query will result in an error because the subquery returns more than one result.

The correct approach is to use the select statement in the INSERT query and hardcode the constant fields:

insert into prices (group, id, price)
select 
    7, articleId, 1.50
from article where name like 'ABC%';

By separating the constant fields from the subquery, you can ensure that only one row is inserted for each result of the subquery.

The above is the detailed content of How to Correctly Insert Multiple Values into a Table Using a Subquery?. For more information, please follow other related articles on the PHP Chinese website!