CODEWITHSUNDEEP
sqlmysql
UmYeah
UmYeah

Reputation: 820

Complicated/Simple SQL Insert: adding multiple rows

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)

Upvotes: 2

Views: 260

Answers (2)

Lluis Martinez
Lluis Martinez

Reputation: 1973

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) )

Upvotes: 1

Peter Lang
Peter Lang

Reputation: 55524

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

Upvotes: 10

Related Questions