Results 1 to 3 of 3
  1. #1
    Join Date
    May 2007
    Posts
    11

    Question Unanswered: Update Trouble ???

    Hi,
    I tried to update a table using this query.

    UPDATE RA1 r

    SET r. OPRND_VAL = (SELECT Rpad(SUBSTR(upd.NEW_GPI_CD,1,instr( r. OPRND_VAL,'*',1)-1),14,'*')FROM GPI_ERR_CRCTN upd
    WHERE rtrim(r. OPRND_VAL,'*') = SUBSTR(upd. GPI_CD,1,instr(r. OPRND_VAL,'*',1)-1) AND upd. CMPLT_REPLC_CD = 'Y')

    WHERE rtrim(r. OPRND_VAL,'*') =
    (SELECT SUBSTR(upd. GPI_CD,1,instr(r. OPRND_VAL,'*',1)-1)
    FROM GPI_ERR_CRCTN upd WHERE upd. CMPLT_REPLC_CD = 'Y' )

    When I try to execute It is giving the following error

    ORA-01427
    ORA-01427: single-row subquery returns more than one row
    Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.





    Please help:
    Last edited by nivin; 02-04-08 at 09:45.

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    The error message is fairly transparent. Your subquery is returning more than 1 row.
    Either the SET clause or the WHERE clause subquery is returning multiple rows. (or possibly both) If it is the where clause, you could either use a multi - row operator instead of a single row operator, alternatively you could make sure that the subquery returns only 1 row. If it is the SET clause subquery that returns multiple rows, you will have to edit the query to return only 1 row.

  3. #3
    Join Date
    May 2007
    Posts
    11
    done... it returned more rows at the set clause....
    thnx man.. :-)

Posting Permissions

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