Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

insert statement with subquery to insert multiple rows.

user635329Nov 2 2011 — edited Nov 3 2011
So I am running the following script using a subquery to return multiple records from the MTL_SYSTEM_ITEMS table and insert them into the TAXPRODCONV, but I get the ora-01427.

ORA-01427: single-row subquery returns more than one row
INSERT INTO TAXPRODCONV
(merchantid,
busnlocn,
userprodcode1,
userprodcode2,
twiprodcode)
VALUES
('01',
'DEFAULT',
(select segment1
        from mtl_system_items
        where segment1 like 'SV%-P%'
    MINUS
    select userprodcode1
         from taxprodconv),
(  select segment1
        from mtl_system_items
        where segment1 like 'SV%-P%'
    MINUS
    select userprodcode1
         from taxprodconv),
 '90000');
Note the merchantid, busnlocn and twiprodcode are constants, only the userprodcode1 and 2 will change from the subqueries I am running. I understand the ora error, because oracle thinks I am trying to insert one row. Is there a way to do this as an standard insert statement, or am I looking at writing a cursor to accomplish this task?
This post has been answered by 647939 on Nov 3 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 1 2011
Added on Nov 2 2011
4 comments
25,208 views