CODEWITHSUNDEEP
sqlsql-server-2012
user3594030
user3594030

Reputation: 11

SQL Inserting Multiple Rows into a Table Using a Select Statement

I have three tables I'm working with: Roles, Permissions, & RolePermissions (which links the two tables together. It has two columns: RoleId and PermissionId).

I added a new row to Permissions so I need to make it to where any role that has a permission = 117 is also equal to the latest permission that I just created (which could be any number). For example, say you have the role of adviser that has permission 117, then you automatically have this new permission (which is the max(id))

This is the code I tried:

INSERT INTO RolePermissions 
VALUES
(
    (SELECT RoleId FROM RolePermissions WHERE PermissionID = '117'), 
    (select max(id)     from Permissions)
);

It says:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I believe it's because there are multiple roles that = 117 so it needs to make multiple rows. I'm a n00b and unsure how to make it multiple rows, would I use a loop?

Thanks

Upvotes: 1

Views: 9184

Answers (1)

Hituptony
Hituptony

Reputation: 2860

You don't have a WHERE clause on your subquery so it's returning every max_ID per permission

try

INSERT INTO RolePermissions VALUES (RoleID, PermissionID, newcolumn)
SELECT RoleID, PermissionID, (SELECT MAX(ID) from Permissions) from 
RolePermissions WHERE PermissionId = 117

To break this down to you:

You're going to insert ito your join table, including your 'new column' which you didn't mention the name...

You're going to select the RoleID, and PermissionID from the original table WHERE your PermissionID = 117 (no single quotes necessary if this is an INT field). You're going to do a sub-query into the last column on the MAX(ID) of the Permissions table, if you want to further isolate the MAX(Id) add the WHERE clause to the sub-query.

This will add all the users with Permission ID = 117 to the table, where they should already have a record. So you will now have two records per person where PermissionId 117, the only difference will be on the additional column, the first record will be null, the second will have the value of MAX(Id)

If you don't want to ADD a new member you may want to update those rows where the permissionId = 117 For that you would:

UPDATE RolePermissions
SET newcolumn = (SELECT MAX(ID) from Permissions)
WHERE PermissionID = 117

Upvotes: 3

Related Questions