ORA-01427 - single-row subquery returns more than one row
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-01427 - single-row subquery returns more than one row

Hybrid View

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    ORA-01427 - single-row subquery returns more than one row

    hi,
    I've 2 tables
    TABLE RE with these col
    RE_ID varchar2(32) primary key
    PROPERTY char(1)

    TABLE RE_NEW with these col
    A_RE_ID varchar2(2)
    B_RE_ID varchar2(6)
    PROPERTY char(1)


    A_RE_ID + B_RE_ID = RE_ID

    The col PROPERTY of RE_NEW is full and PROPERTY of RE is empty.
    I'd like to populate PROPERTY of RE with same values of RE_NEW.

    my query is

    UPDATE RE
    SET PROPERTY = (SELECT PROPERTY
    FROM RE_NEW
    WHERE A_RE_ID=SUBSTR(RE.RE_ID,1,2)
    AND B_RE_ID=SUBSTR(RE.RE_ID,3,6))

    where I run this query I get this error:

    ORA-01427 - single-row subquery returns more than one row

    What I wrong??

    Thanks
    Raf

  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Re: ORA-01427 - single-row subquery returns more than one row

    You can see in Oracle Error message what is wrong, that is very clearly. Try to execute query with your WHERE clause and get count(*) > 1.

    Originally posted by raf
    ORA-01427 - single-row subquery returns more than one row
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    You inner select is returning more than one row for one/many of your update statements. You need to confirm that the query returns one row for each update.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  4. #4
    Join Date
    Apr 2001
    Posts
    19
    hi
    what jovery says is true u r inner select statment is returning more than one row in any updates. first make sure that u r inner select statment is returning single row. or u can use "in" clause insted of where clause in u r update statment.

    good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width