CODEWITHSUNDEEP
mysqlsqlsql-insert
ptvty
ptvty

Reputation: 5664

MySQL - Bulk insertion into table from the result of a select query

I want to grab all rows from table items where id > 100

SELECT `id` FROM `items` WHERE `id` > 100

And for each of these rows returned from above select, insert a new row into the item_tags table;

INSERT INTO `item_tags` (`item_id`, `tag_id`) VALUES (107, 123)
INSERT INTO `item_tags` (`item_id`, `tag_id`) VALUES (114, 123)
.
.
.
INSERT INTO `item_tags` (`item_id`, `tag_id`) VALUES (299, 123)

Can I do this in a single query in MySQL?

Upvotes: 1

Views: 32

Answers (1)

GMB
GMB

Reputation: 222462

Consider the INSERT ... SELECT ... syntax:

INSERT INTO `item_tags` (`item_id`, `tag_id`) 
SELECT `id`, 123 FROM `items` WHERE `id` > 100

This will insert one record in item_tags for each record in items having id > 100, with a fixed value of 123 for column tag_id.

Upvotes: 1

Related Questions