oracle - Trouble performing a SQL insert using selects where more than 1 row is to be inserted -
i'm not strongest sql developer (kinda new) thought i'm trying do, i'm receiving error.
basically, i'm trying perform mass insert based on selects. value of first select in statement below returns 1 groupid, 2nd statement returns few hundred requestcategory id's. trying perform these few hundred inserts statement below:
insert m_grouprequestcategory ( groupid, requestcategoryid ) values ( (select groupid esaws.m_group name = 'administration group'), (select requestcategoryid esaws.m_grouprequestcategory groupid = (select groupid esaws.m_group name = 'customer service group')) );
my issue is, sql returning following error:
error report - sql error: ora-01427: single-row subquery returns more 1 row 01427. 00000 - "single-row subquery returns more 1 row" *cause: *action:
can please explain me i'm doing wrong, , need change working?
edit: clear, im trying come way not have write few hundred indivdual inserts.
i suspect want:
insert m_grouprequestcategory ( groupid, requestcategoryid ) select distinct g.groupid, r.requestcategoryid esaws.m_group g cross join esaws.m_grouprequestcategory r g.name = 'administration group' , r.groupid = ( select groupid esaws.m_group name = 'customer service group') , not exists(select * esaws.m_grouprequestcategory r2 r2.groupid = g.groupid , r2.requestcategoryid = r.requestcategoryid) ;
which copying of request categories 'customer service group' 'administration group', if understand correctly.
Comments
Post a Comment