SQL Insert with sub-select and multiple rows

Question

I have an existing documents table and a new permissions table. I want to create a script that will give all permissions to all of the existing documents. The documents_permissions table will need the document_id and permission_id (1,2,3). So for each document I will need to insert 3 rows. This is where I am at atm:

INSERT INTO `documents_permissions` (`document_id`, `permission_id`)
SELECT `id`, '1' FROM `documents`

but I'd need to repeat that for each permission. What is the best way to do this?

Joe G Joseph · Accepted Answer · 2012-10-19 09:39:04Z

4

try this:

USe CROSS JOIN

INSERT INTO `documents_permissions` (`document_id`, `permission_id`)
select  `id`,a.permissions
 from(
select 1 as permissions union all
select 2 union all
select 3 )a
cross join
`documents` d

answered Oct 19, 2012 at 9:39

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

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

3 Comments

xylar Over a year ago

Thanks. So if I wanted to add more permissions would I update it to have select 3 union all select 4 union all ?

PyQL Over a year ago

You do not need to use UNION ALL unless you have duplicate values like 1,1,2,3,4,4 etc. If you do not have duplicates, UNION keyword is enough

Joe G Joseph Over a year ago

@Bader: UNION ALL is faster than UNION

PyQL · Accepted Answer · 2012-10-19 09:39:33Z

1

INSERT INTO `documents_permissions` (`document_id`, `permission_id`)
select id, pid from `documents`
cross join (select 1 as pid union select 2 union select 3)X

answered Oct 19, 2012 at 9:39

PyQL

1,8303 gold badges18 silver badges23 bronze badges

Collectives™ on Stack Overflow

SQL Insert with sub-select and multiple rows

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related