Combine INSERT INTO and SELECT for multiple VALUES

Question

I know that the most common way for inserting multiple new rows into a table is:

INSERT INTO fruits (fruit, colorId)
VALUES ('apple', 1),
       ('orange', 2),
       ('strawberry', 3);

I also know that I can insert results obtained from a SELECT, even using custom values:

INSERT INTO fruits (fruit, colorId)
SELECT 'pear', id
FROM colors
WHERE color = 'green';

The thing is that, using any of those options (or maybe a different one, which I do not know), I would like to insert multiple values using the result obtained in such a query. For instance:

SELECT id
FROM colors
WHERE color = 'yellow';

would return a single value 4 (the id for yellow), which I would like to reuse in a multiple insert query such as

INSERT INTO fruits (fruit, colorId)
VALUES ('banana', id),
       ('lemon', id);

(where id should be a 4). Is it possible?

EDIT: By the way, I would also like to avoid using subqueries like

INSERT INTO fruits (fruit, colorId)
VALUES ('banana', (SELECT id FROM colors WHERE color = 'yellow')),
       ('lemon',  (SELECT id FROM colors WHERE color = 'yellow'));

@PaulSpiegel I mean subqueries in the way I wrote in my example, which implies a SELECT per each inserted value. Of course, if there is a subquery that avoids that, I am very open to it. — AugSB, Mar 04 '20 at 12:18
I'm quite sure MySQL is able to recognize that it's the same query and will cache the result. Also you can write the application code the way that you will know the code of 'yellow'. Eg. in HTML `` — Paul Spiegel, Mar 04 '20 at 12:20
I've been always told that the efficiency of a SELECT inside another SELECT is very poor, but I will try it anyway. Thanks! — AugSB, Mar 04 '20 at 12:26
"efficiency of a SELECT inside another SELECT is very poor" - Well, that is not true. It depends. — Paul Spiegel, Mar 04 '20 at 12:34

Salman A · Answer 1 · 2020-03-04T12:18:02.890

2

Like so:

INSERT INTO fruits(fruit, colorid)
SELECT names.name, colors.id
FROM colors
CROSS JOIN (
    SELECT 'banana' AS name UNION ALL
    SELECT 'lemon'
) AS names
WHERE colors.color = 'yellow'

In MySQL 8 you could use a table value constructor:

INSERT INTO fruits(fruit, colorid)
SELECT names.column_0, colors.id
FROM colors
CROSS JOIN (VALUES
    ROW('banana'),
    ROW('lemon')
) AS names
WHERE colors.color = 'yellow'

edited Mar 04 '20 at 12:18

answered Mar 04 '20 at 12:08

Salman A

262,204
82
430
521

Sorry, I am restricted to MySQL 5.7, so the second option is not valid for me. Can you elaborate on the first option? It seems it requires many SELECT and UNION ALL. How efficient would be for much more values (hundreds, maybe thousands...)? – AugSB Mar 04 '20 at 12:22
I generally don't worry about efficiency of an insert statement. You could consider the other answer that uses a user variable. – Salman A Mar 04 '20 at 12:25

score 1 · Accepted Answer · answered Mar 04 '20 at 12:16

1

BTW, you can do it like below:

DECLARE @Id INT
SET @Id = (SELECT id FROM colors WHERE color = 'yellow')
INSERT INTO fruits (fruit, colorId)
VALUES ('banana', @Id),
   ('lemon',  @Id);

answered Mar 04 '20 at 12:16

Vijesh

795
3
9
23

Combine INSERT INTO and SELECT for multiple VALUES

2 Answers2