Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Red face Unanswered: ORA-01427: single-row subquery returns more than one row

    update test a
    set a.UPC = (select concat('0', aa.UPC)
    from test aa
    where length(rtrim(aa.UPC)) = 11)
    where length(rtrim(a.UPC)) = 11

    when its set to 9 its working, when its set to 1 or 11
    it gives me an error

    ERROR at line 2:
    ORA-01427: single-row subquery returns more than one row

    Please help

  2. #2
    Join Date
    Jul 2003
    the following part of your query returns more than one row:
    PHP Code:
    select concat('0'aa.UPC)
    from test aa
         where length
    (rtrim(aa.UPC)) = 11 
    If you run that alone you will get more than one row.
    To get only one row I suggest using DISTINCT or where rownum = 1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2003
    Bangalore, INDIA

    Thumbs up


    Suppose that the SET subquery returns five rows for the row you are updating. Which row should the database choose in order to assign values to the columns listed on the left side of the SET clause? The first row? The third row? The value in the database is undefined and the database does not choose a value at random, hence the error occurs.

Posting Permissions

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