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

Question

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?

You can use INSERT ... SELECT... anda since MySql 8.0.19 You can use INSERT .. TABLE — Marcos Echagüe
– Marcos Echagüe, Commented Jan 19, 2020 at 18:41

GMB · Accepted Answer · 2020-01-19 18:19:27Z

1

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.

answered Jan 19, 2020 at 18:19

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related