Multiple inserts of multi-valued subqueries

Question

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!

score 1 · Answer 1 · answered Dec 17 '19 at 03:42

1

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

answered Dec 17 '19 at 03:42

zip

3,938
2
11
19

Thank you for the answer, this works! However I don't need to worry about duplicates and it seems like `UNION ALL` executes faster, so I marked the other answer as correct. – Tex4066 Dec 17 '19 at 17:31

score 1 · Accepted Answer · answered Dec 17 '19 at 03:42

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%';

Thank you, this works! And since this is mostly test data, I don't have to worry about duplicates. — Tex4066, Dec 17 '19 at 17:27

Multiple inserts of multi-valued subqueries

2 Answers2