CODEWITHSUNDEEP
sqlpostgresql
Семен Немытов
Семен Немытов

Reputation: 307

Insert with select posgresql

I want to insert new roles into my app, but only for several users. I can get all their ids by select statement.

So the role for all of them is 'COOL_ROLE'

Table account_role with roles looks like

+------------+-------+
| account_id | roles |
+------------+-------+

And my statement should just insert many ids with the same role I have tried

INSERT INTO accout_role (accout_id, roles)
VALUES((SELECT..),'COOL_ROLE')

but I get error org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [21000]: ERROR: more than one row returned by a subquery used as an expression

When I remove () in expression

INSERT INTO accout_role (accout_id, roles)
VALUES(SELECT..,'COOL_ROLE')

I get org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [42601]: ERROR: syntax error at or near "select"

How can I insert several ids with the same role?

Upvotes: 1

Views: 828

Answers (2)

Andronicus
Andronicus

Reputation: 26026

Use insert into ... select:

insert into account_role (accout_id, roles)
select id, 'COOL_ROLE'
from account
where ...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Remove the values:

INSERT INTO account_role (account_id, roles)
    SELECT account_id, 'COOL_ROLE'
    FROM . . . ;

Upvotes: 1

Related Questions