How to insert new rows for each foreign key from a table-CodePudding

I want to make a query to add for each user all the id_job, id_role pairs from the user with id 0 (id_user, default user). Is it possible in mysql?

user_role table

id_user	id_job	id_role
0	1	1
0	2	2
1	1	1
1	2	2
2	3	1

These are the dates I want to get to. As you can see in the first table, for the user with id 2, we don't have id_job and id_role [(1,1), (2,2)] and I want to run a query that populates the missing pairs for this user.

user_role_updated table

id_user	id_job	id_role
0	1	1
0	2	2
1	1	1
1	2	2
2	3	1
2	1	1
2	2	2

Thank you in advance!

CodePudding user response：

insert into user_role(id_user, id_job, id_role)
with cte_0 as(select id_user
                     , id_job
                     , id_role 
              from user_role 
              where id_user = 0)
select distinct ur.id_user
                , cte_0.id_job
                , cte_0.id_role
from user_role ur
     , cte_0
where ur.id_user <> 0
and (ur.id_user, cte_0.id_job, cte_0.id_role) not in (select id_user, id_job, id_role from user_role where id_user <> 0)

Here is a demo