Complicated/Simple SQL Insert: adding multiple rows

Question

I have a table connecting principals to their roles. I have come upon a situation where I need to add a role for each user. I have a statement SELECT id FROM principals which grabs a list of all the principals. What I want to create is something like the following:

INSERT INTO role_principal(principal_id,role_id)
VALUES(SELECT id FROM principals, '1');

so for each principal, it creates a new record with a role_id=1. I have very little SQL experience, so I dont know if I can do this as simply as I would like to or if there is some sort of loop feature in SQL that I could use.

Also, this is for a mySQL db (if that matters)

Please stop thinking in terms of looping, looping is bad on databases, think interms of affecting sets of data instead. — HLGEM
– HLGEM, Commented Jan 13, 2010 at 20:18

Peter Lang · Accepted Answer · 2010-01-13 20:10:35Z

10

Use VALUES keyword if you want to insert values directly. Omit it to use any SELECT (where column count and type matches) to get the values from.

INSERT INTO role_principal(principal_id,role_id)
    (SELECT id, 1 FROM principals);

edited Jan 13, 2010 at 20:10

answered Jan 13, 2010 at 19:56

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

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

2 Comments

Larry Lustig Over a year ago

When using INSERT . . . SELECT on most SQL systems, you omit the parens around the SELECT statement. I think it's the same in MySQL, but not 100% sure.

UmYeah Over a year ago

I replaced 1 with (SELECT id FROM roles WHERE...) so that it could work for any role. But thank you for guiding me down the right path.

Lluis Martinez · Accepted Answer · 2010-01-13 20:21:43Z

1

To avoid duplicates is useful to add a subquery :

INSERT INTO role_principal(principal_id,role_id) (SELECT id, 1 FROM principals p WHERE NOT EXISTS (SELECT * FROM role_principal rp WHERE rp.principal_id=p.id AND role_id=1) )

answered Jan 13, 2010 at 20:21

Lluis Martinez

1,9848 gold badges28 silver badges42 bronze badges

1 Comment

UmYeah Over a year ago

Thanks for the additional answer. I know in my specific situation I wont have any duplicates but it is certainly good to know how I would handle that situation.

Collectives™ on Stack Overflow

Complicated/Simple SQL Insert: adding multiple rows

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related