CODEWITHSUNDEEP
mysqlsql
Mr_Chimp
Mr_Chimp

Reputation: 6907

Insert multiple rows with given values and values from related table

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.

Upvotes: 0

Views: 121

Answers (2)

user1726343
user1726343

Reputation:

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

Upvotes: 2

Sashi Kant
Sashi Kant

Reputation: 13465

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

Upvotes: 1

Related Questions