Correct Methods for Inserting Data into SQL Tables Using Multi-Result Subqueries

Problem Background and Error Analysis

In database operations, there is often a need to insert data into target tables based on query results. The user attempted the following SQL statement:

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

This statement produces an error during execution: "Subquery returned more than 1 value." This occurs because the VALUES clause expects a single scalar value, while the subquery select articleId from article WHERE name LIKE 'ABC%' may return multiple articleId values.

Correct Solution

The proper implementation uses the INSERT INTO...SELECT statement:

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

This statement works by: first executing the SELECT query to filter all records from the article table where the name starts with 'ABC', then generating a new row for each matching record containing the fixed value 7, the corresponding articleId, and price 1.50, and finally bulk inserting these rows into the prices table.

Technical Principle Deep Dive

The core advantage of the INSERT INTO...SELECT statement lies in its ability to handle multi-row data insertion. Unlike the VALUES clause, which only accepts explicit value lists, the SELECT clause can return any number of rows, with each row's columns matching the insert columns of the target table.

In terms of performance, this bulk insertion method is generally more efficient than looping through single-row inserts, as it reduces interaction with the database and allows the query optimizer to optimize the entire operation.

Practical Application Extensions

This pattern can be extended to more complex scenarios:

• Dynamic Value Calculation: The SELECT clause can use expressions, such as price * 1.1 to calculate tax-inclusive prices
• Multi-Table Joins: Retrieve data from multiple tables via JOIN
• Conditional Insertion: Implement conditional data insertion combined with WHERE conditions

For example, fetching data from related tables:

INSERT INTO prices (group, id, price)
SELECT 7, a.articleId, p.base_price * 1.1
FROM article a
JOIN product_prices p ON a.product_id = p.id
WHERE a.name LIKE 'ABC%';

Best Practice Recommendations

In actual development, it is recommended to:

1. Always verify that the data returned by the subquery meets expectations
2. Execute important data insertion operations within transactions to ensure data consistency
3. Consider using EXISTS or NOT EXISTS to avoid duplicate inserts
4. For large-volume inserts, evaluate the use of bulk operations or partitioning strategies