CODEWITHSUNDEEP
mysqlsql
green green grass
green green grass

Reputation: 549

Performing multiple inserts with Selected data

I want to perform this sudo query:

INSERT INTO table1 (foreign_key, meta_key, meta_value)
VALUES (
     (SELECT id FROM table2 WHERE condition = 'met'), 'first meta key', 'first meta value'
)
VALUES (
     (SELECT id FROM table2 WHERE condition = 'met'), 'second meta key', 'second meta value'
)
VALUES (
     (SELECT id FROM table2 WHERE condition = 'met'), 'third meta key', 'third meta value'
)

Where the strings after the SELECT statements are the second and third arguments for the INSERT

I think I have the INSERT SELECT syntex basically right, but I'm not certain about the multiple INSERTS. I know I can do it like this with a normal INSERT statement, but I'm uncertain about the SELECT being inside the VALUES clause.

I'm working with a live database and although I have backed everything up, I want to keep direct experimentation to a minimum.

Upvotes: 2

Views: 40

Answers (2)

Hrabal
Hrabal

Reputation: 2525

you can use UNION:

INSERT INTO table1 (foreign_key, meta_key, meta_value)
SELECT id, 'first meta key', 'first meta value' FROM table2 WHERE condition = 'met'
UNION
SELECT id, 'second meta key', 'second meta value' FROM table2 WHERE condition = 'met'
UNION
SELECT id, 'third meta key', 'third meta value' FROM table2 WHERE condition = 'met'

Upvotes: 1

Jaugar Chang
Jaugar Chang

Reputation: 3196

You need add comma between values.

INSERT INTO table1 (foreign_key, meta_key, meta_value)
VALUES (
     (SELECT id FROM table2 WHERE condition = 'met'), 'first meta key', 'first meta value'
),
(
     (SELECT id FROM table2 WHERE condition = 'met'), 'second meta key', 'second meta value'
),
(
     (SELECT id FROM table2 WHERE condition = 'met'), 'third meta key', 'third meta value'
)

And be sure every sub query return 1 row only.

Try this Sql Fiddle Demo.

Or use union as a normal way:

INSERT INTO table1 (foreign_key, meta_key, meta_value)
SELECT id, 'first meta key' , 'first meta value'  FROM table2 WHERE condition = 'met')
UNION ALL
SELECT id, 'second meta key', 'second meta value' FROM table2 WHERE condition = 'met')
UNION ALL
SELECT id, 'third meta key' , 'third meta value'  FROM table2 WHERE condition = 'met')

Upvotes: 1

Related Questions