Insert multiple rows with given values and values from related table

Question

I have a large insert query like this:

INSERT INTO video_tags
  (vid_id, tag_id)
VALUES
  (1,7),
  (2,46),
  (2,52) Etc. Etc.

However I don't have the tag_ids to insert, only the tag string. Therefore I need to lookup the tag id as I am inserting them. The tags are stored in a lookup table with two columns:

+-----+---------+
| id  | int     |
| tag | varchar |
+-----+---------+

I want to do something like this:

INSERT INTO video_tags
  (vid_id, tag_id)
VALUES
  (1, SELECT id FROM tags WHERE tag = 'banana'),
  (2, SELECT id FROM tags WHERE tag = 'tree')
  (2, SELECT id FROM tags WHERE tag = 'chimps')

...but this doesn't work and anyway looks cumbersome and inefficient.

I have also seen this done:

INSERT INTO video_tags
  (vid_id, tag_id)
VALUES
  SELECT '1', id FROM tags WHERE tag = 'banana')

...but I can't get this to work with multiple selects.

How about making a table that has your vid_ids and tag strings, then doing something like insert into...from (select vid_id,tag_id — user1726343
– user1726343, Commented Nov 8, 2012 at 12:50
Unless I'm misunderstanding you, I have such a table. It's the one I'm trying to insert values into! — Mr_Chimp
– Mr_Chimp, Commented Nov 8, 2012 at 13:02
The one you are inserting into doesn't have vid_ids and tag strings. It has vid_ids and tag_ids. — user1726343
– user1726343, Commented Nov 8, 2012 at 13:04
Ah, I get you. Seems to go against the idea of keeping the data normalised though. — Mr_Chimp
– Mr_Chimp, Commented Nov 8, 2012 at 13:13

user1726343 · Accepted Answer · 2012-11-08 13:23:17Z

2

Your query could go something like this:

CREATE TABLE rawdata
    (`vid_id` int, `tag_name` varchar(10))
;

INSERT INTO rawdata
    (`vid_id`, `tag_name`)
VALUES
    (1, 'banana'),
    (2, 'tree'),
    (2, 'chimps')
;

INSERT INTO video_tags
SELECT rawdata.vid_id, tag.id FROM
rawdata
LEFT JOIN tag ON tag.tag = rawdata.tag_name
;

DROP TABLE rawdata

http://sqlfiddle.com/#!2/89171/2

answered Nov 8, 2012 at 13:23

user1726343

Sign up to request clarification or add additional context in comments.

1 Comment

Mr_Chimp Over a year ago

Ah I see what you mean now. That makes sense.

Sashi Kant · Accepted Answer · 2012-11-08 12:44:12Z

1

Try this ::

INSERT INTO video_tags
  (vid_id, tag_id)
VALUES
  (1, (SELECT id FROM tags WHERE tag = 'banana')),
  (2, (SELECT id FROM tags WHERE tag = 'tree')),
  (2, SELECT id FROM tags WHERE tag = 'chimps'));

answered Nov 8, 2012 at 12:44

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

1 Comment

Mr_Chimp Over a year ago

Hmm. This does work but it takes 0.1s for just 5 entries. If I try my whole query I get an out of resources error. There must be a more efficient way of doing it...?

Collectives™ on Stack Overflow

Insert multiple rows with given values and values from related table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related