CODEWITHSUNDEEP
sqlinsertsubquery
Tex4066
Tex4066

Reputation: 357

Multiple inserts of multi-valued subqueries

I found the single insert answer from this question: How can I insert values into a table, using a subquery with more than one result?. However, I'm looking for a way to do multiple inserts in this fashion.

Single-Insert version WORKING! (using the previous answer...):

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

Multi-Insert version (Failing)

INSERT INTO prices (group, id, price)
(select 7, articleId, 1.50 FROM article WHERE name like 'ABC%'),
(select 9, articleId, 2.50 FROM article WHERE name like 'DEF%');

Is there a method of doing these multiple inserts of multi-column subqueries with a simple-enough syntax?

Thanks!

Upvotes: 0

Views: 38

Answers (2)

sticky bit
sticky bit

Reputation: 37472

You can use UNION ALL. It does a union of the two result sets while keeping duplicates.

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

Upvotes: 1

zip
zip

Reputation: 4061

Do it this way:

INSERT INTO prices (group, id, price)
select 7, articleId, 1.50 FROM article WHERE name like 'ABC%'
union
select 9, articleId, 2.50 FROM article WHERE name like 'DEF%'

You need to have the record inserted coming as one source

Upvotes: 1

Related Questions