Insert into multiple selects from different tables

Question

I have tables users (id, email), permissions (id, description) and users_permissions (user_id, permission_id, created) with many to many relation.

I need to select user with some email and assign to him all permissions from table permissions, which he does not have.

Now I am trying to assign at least all permissions, but I am getting error

Subquery returns more than 1 row

My query:

insert into `users_permissions` (`user_id`, `permission_id`, `created`) select (select `id` from `users` where `email` = '[email protected]') as `user_id`, (select `id` from `permissions`) as `permission_id`, now() as `created`;

Madhur Bhaiya · Accepted Answer · 2019-06-25 09:12:44Z

If a subquery (inside SELECT) returns more than one row, MySQL does not like it.

Another way to achieve your requirement is using CROSS JOIN between Derived Tables (subquery in the FROM clause):

INSERT INTO `users_permissions` (`user_id`, `permission_id`, `created`) SELECT u.id, p.id, NOW() FROM users AS u CROSS JOIN permissions AS p WHERE u.email = '[email protected]'

Thank you, it works as I expected :) Can it somehow insert only permissions which user does not have in table users_permissions ?
@domanskyi Yes. In that case you will need a left join with user_permissions table and consider only those cases where the join results in null

Collectives™ on Stack Overflow

Insert into multiple selects from different tables

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related