CODEWITHSUNDEEP
mysqlsql
domanskyi
domanskyi

Reputation: 753

Insert into multiple selects from different tables

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`;

Upvotes: 1

Views: 99

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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]'

Upvotes: 2

Related Questions