How to Handle Multiple Results from a Subquery During SQL Server INSERT?

Solving Subquery Values Insertion with Multiple Results

You have two tables, Article and Prices, in SQL Server. You want to insert certain IDs from Article into Prices using a subquery to select the IDs. However, you face the error of "subquery has more than 1 value" when using the below code:

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

The issue arises because the subquery returns multiple IDs since there could be multiple articles matching the 'ABC%' criteria. In such cases, you need to modify your query as follows:

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

This revised query explicitly specifies the constant values (group: 7, price: 1.50) alongside the subquery to select the article IDs. By separating the constant values, you avoid subquery evaluation for each ID, resolving the issue of multiple result values.

The above is the detailed content of How to Handle Multiple Results from a Subquery During SQL Server INSERT?. For more information, please follow other related articles on the PHP Chinese website!