CODEWITHSUNDEEP
sqlpostgresql
Tuco
Tuco

Reputation: 1052

Postgresql: Inserting values for each row returned by a select

Hi I'm having trouble trying to figure out how to build the following query:

INSERT INTO role_permissions (role_id, permission)
VALUES (
   (SELECT role_id
    FROM role_permissions
    WHERE permission = 'manage_admins'),
   'create_admins');

INSERT INTO role_permissions (role_id, permission)
VALUES (
   (SELECT role_id
    FROM role_permissions
    WHERE permission = 'manage_admins'),
   'edit_admins');

So basically some context, there is a permission called manage_admins that would allow users to edit/create, but now I need to separate this permission into 2 different permissions. The thing is I also have a role_permissions table where I store all of a role's permissions.

So I need to insert the 2 new permissions for every role that had the old permission, the example I provided is failing because the SELECT query is returning multiple values. So this is what I'm having trouble with, so any help would be greatly appreciated.

Upvotes: 12

Views: 12085

Answers (2)

user330315
user330315

Reputation:

Get rid of values:

INSERT INTO role_permissions (role_id, permission)
SELECT rp.role_id, t.permission
FROM role_permissions rp
  cross join (values ('edit_admins'), ('create_admins')) as t(permission)
WHERE rp.permission = 'manage_admins';

The cross join will create two rows with the same role_id and the select will then insert those IDs together with the new permission name into the table.

Upvotes: 15

Laurenz Albe
Laurenz Albe

Reputation: 246298

To insert multiple rows, just omit VALUES:

INSERT INTO role_permissions (...)
SELECT ...

Upvotes: 3

Related Questions