DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

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

  1. #1
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43

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

    I have two tables with two similar fields point_value and id. I am trying to update table_tmp based on values from table_assn but keep getting error message ORA-01427: single-row subquery returns more than one row. I will appreciate any help on getting the update accomplished.


    update table_tmp a
    set a.point_value = (select b.point_value
    from table_assn b,
    where b.id = a.id
    and b.point_value != a.point_value)
    OCP 8i DBA
    I'm still learning

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The condition ...
    Code:
     where b.id = a.id
    and b.point_value != a.point_value
    ... is not enough to ensure that you only get one row back from table_assn for each row in table_tmp.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43
    Thanks for the prompt response. You're right, that's what is causing the error message. Any suggestion on how I can fix this?
    OCP 8i DBA
    I'm still learning

  4. #4
    Join Date
    Jun 2000
    Location
    GA
    Posts
    43
    Thanks guys, I was able to resolve it. Here is my query:

    I created a dummy table
    create table test_dummy
    as (select b.point_value
    from table_assn b,
    where b.id = a.id
    and b.point_value != a.point_value)

    then

    update table_tmp
    set point_value = ( select point_value from table_assn where table_assn.id = table_tmp.id )
    where EXISTS ( select point_value from table_assn where table_assn.id = table_tmp.id)
    OCP 8i DBA
    I'm still learning

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