Inserting into table values found from previous query

I have table, let’s say 'ROLEE'., which contain columns ROLE, ACCESSENTRY. I need to find first rows that ACCESSENTRY is equal to ‘A’ and for these rows insert into that table new rows with fetched ‘ROLE‘ and given ACCESSENTRY. So let’s say within this query:

select role from ROLEE where ACCESSENTRY='A'

I get 2 rows with values: ADMIN, USER and for these roles (ADMIN, USER) I need to insert

INSERT INTO ROLEE
values ('ADMIN', 'ACCESS')
INSERT INTO ROLEE
    values ('USER', 'ACCESS')

I tried with these, but it does not work:

INSERT INTO ROLEE
values (role, 'ACCESS') where role in (
select role from ROLEE where ACCESSENTRY='A')

Answer

Please use below insert statement,

insert into ROLEE (select role, 'ACCESS' from ROLEE where ACCESSENTRY='A');

The right way is to specify the column names,

insert into ROLEE(column1, column2) (select role, 'ACCESS' from ROLEE where ACCESSENTRY='A'); -- Provide the respective column names in colum1 and column2

Advertisement

Answer