CODEWITHSUNDEEP
mysqlsql-insertinsert-select
AugSB
AugSB

Reputation: 269

Combine INSERT INTO and SELECT for multiple VALUES

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

Upvotes: 1

Views: 1647

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

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'

Upvotes: 2

Vijesh
Vijesh

Reputation: 815

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);

Upvotes: 1

Related Questions