CODEWITHSUNDEEP
mysqlsqlinsertsubquery
usumoio
usumoio

Reputation: 3568

MySQL multiple insert of a variable number of rows based on another table

I have 3 tables one is called subscribers, one users, and one is called blogs. They each look as followed. 

CREATE TABLE IF NOT EXISTS `subscribers` (
   `user_id` int(11) NOT NULL,
   `blog_id` int(11) NOT NULL,
);

CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL,
    `name` varchar(255)
);

CREATE TABLE IF NOT EXISTS `blogs` (
    `id` int(11) NOT NULL,
    `name` varchar(255),
    `user_id` int(11)
);

I have a situation where an API creates blog records in our db automatically, these automatically created blogs are all assigned the same user id (in this case 123) so that it is known that they really come from and belong to the importing API. Now after the fact a user needs to be created for these API originating blogs to look after them. I created user 123 using basic inserting, but now I must subscribe this user to every blog that they own via an insert or a series of inserts. I want to know if it is possible to do this with one query. Something like this, but I realize what I wrote will not work:

INSERT INTO `subscribers` (`user_id`,`blog_id`) 
VALUES 
(123, (SELECT id FROM `blogs` WHERE `user_id` = 123));

Is it possible to do a multi-insert based on a variable number of rows in some way, I have researched and I cannot find an answer yet. Thank you for your help.

Upvotes: 1

Views: 1496

Answers (1)

Hogan
Hogan

Reputation: 70538

This is how to do it:

INSERT INTO `subscribers` (`user_id`,`blog_id`) 
   SELECT 123, id FROM `blogs` WHERE `user_id` = 123;

or even

INSERT INTO `subscribers` (`user_id`,`blog_id`) 
   SELECT user_id, id FROM `blogs` WHERE `user_id` = 123;

Upvotes: 9

Related Questions