insert multiple rows into sql using in statement

Question

I am trying to write an sql script to do a bulk insert. I need it to add the users that are managers into the manager's group. I tried to write it like this

INSERT INTO group_member (group_id, user_id) VALUES ((SELECT group_id FROM user_group WHERE group_name = 'Manager') , (SELECT user_id 
FROM user WHERE manager=1 and user_status = 1));

but I am getting this error

Subquery returns more than 1 row

I understand the error but am not sure how to work around it so that I do not miss any users.

When run there can be 0 to many managers, not sure if that will make a difference.

sql version: 5.6.27

CREATE TABLE user_group(
    group_id INT(11) NOT NULL AUTO_INCREMENT,
    group_name VARCHAR(128) NULL DEFAULT NULL,
    PRIMARY KEY (group_id)
);

CREATE TABLE user (
    user_id INT(11) NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(128) NULL DEFAULT NULL,
    manager INT(11) NOT NULL
    user_status INT(11) NOT NULL
    PRIMARY KEY (user_id)
);

CREATE TABLE group_member (
    group_id INT(11) NOT NULL,
    user_id INT(11) NOT NULL,
    PRIMARY KEY (group_id, user_id)
);

can you please post the table definitions please

RoMEoMusTDiE
– RoMEoMusTDiE

2018-04-30 20:49:03 +00:00
Commented Apr 30, 2018 at 20:49 — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Apr 30, 2018 at 20:49
which sql version?

RoMEoMusTDiE
– RoMEoMusTDiE

2018-04-30 20:49:09 +00:00
Commented Apr 30, 2018 at 20:49 — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Apr 30, 2018 at 20:49
@maSTAShuFu updated

Daffy13
– Daffy13

2018-04-30 21:10:26 +00:00
Commented Apr 30, 2018 at 21:10 — Daffy13
– Daffy13, Commented Apr 30, 2018 at 21:10

Daffy13 · Accepted Answer · 2018-05-01 01:28:44Z

1

You want insert . . . select:

INSERT INTO group_member (group_id, user_id)
    SELECT g.group_id, u.user_id
    FROM (SELECT group_id FROM user_group WHERE group_name = 'Manager') g CROSS JOIN
         (SELECT user_id FROM user WHERE manager = 1 and user_status = 1) u;

If the group already has members, you might want to filter them out.

You can also write this as:

INSERT INTO group_member (group_id, user_id)
    SELECT g.group_id, u.user_id
    FROM user u JOIN
         user_group g
         ON g.group_name = 'Manager' AND
            (u.manager = 1 and u.user_status = 1);

edited May 1, 2018 at 1:28

Daffy13

51910 silver badges24 bronze badges

answered Apr 30, 2018 at 21:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

LSerni Over a year ago

@Daffy13 I'm a bit surprised. On my 5.7.21 MySQL, both versions result in a syntax error (ERROR 1064 (42000)) due to the word "group" not being escaped. The MySQL manual page even lists GROUP as a forbidden name (dev.mysql.com/doc/refman/5.6/en/keywords.html). Have you set some special configuration option?

Daffy13 Over a year ago

my real table is named user_group, so it worked for me

Daffy13 Over a year ago

@LSerni should I update the question with that info. I was not thinking it was going to matter at first

LSerni Over a year ago

@Daffy13 yes, you definitely should. I wonder why my code did not work though: with this modification, it should have from the start. Was that the only difference? You should frame your question so that it follows your code as close as possible, otherwise you might receive nonworking - or even harmful - answers. That's was why maSTAShuFu asked for your table definitions.

Daffy13 Over a year ago

@LSerni my guess is that I messed something up. it is working for me now

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

I need it to add the users that are managers into the manager's group

INSERT INTO group_member (group_id, user_id)
SELECT (SELECT group_id FROM `group` WHERE group_name = 'Manager'),
       user_id 
FROM user WHERE manager=1 and user_status = 1;

Of course, there must be only one group with the name of Manager.

Test

mysql> SELECT * FROM `user`;
+---------+-------------+---------+
| manager | user_status | user_id |
+---------+-------------+---------+
|       1 |           1 |       1 |
|       1 |           1 |       2 |
+---------+-------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM `group`;
+----------+------------+
| group_id | group_name |
+----------+------------+
|       17 | Manager    |
+----------+------------+
1 row in set (0.00 sec)

mysql> INSERT INTO group_member (group_id, user_id)
    ->     SELECT (SELECT group_id FROM `group` WHERE group_name = 'Manager'),
    ->            user_id
    ->     FROM user WHERE manager=1 and user_status = 1;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * FROM group_member;
+----------+---------+
| group_id | user_id |
+----------+---------+
|       17 |       1 |
|       17 |       2 |
+----------+---------+
2 rows in set (0.00 sec)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 30, 2018 at 20:49

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

1 Comment

LSerni Over a year ago

@Daffy13 you're correct: group is a keyword and should have been escaped. Amended answer.

Collectives™ on Stack Overflow

insert multiple rows into sql using in statement

2 Answers 2

5 Comments

Test

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Test

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related