update scirpt got ora-01427 when try to update from another table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: update scirpt got ora-01427 when try to update from another table

Hybrid View

  1. #1
    Join Date
    Mar 2007
    Posts
    48

    update scirpt got ora-01427 when try to update from another table

    I am not sure why this is wrong, or how to get around this? Can any SQL experts help?

    update ehtemp.application_xml_q2007 a
    set a.xml_name = b.xml_name from ehtemp.application_xml_info_add where a.form_id = b.form_id;

    I get ORA-00933: SQL command not properly ended

    When I do this:

    update application_xml_q2007 a
    set a.xml_name = (select b.xml_name from application_xml_info_add b where a.form_id b.form_id);
    I get:
    ORA-01427: single-row subquery returns more than one row

    How can I fix my script to make update work?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Your second query has a basically correct syntax but, problem is your inline query should return a single value and as Oracle complains it's returning several rows.

    By the way... on
    select b.xml_name from application_xml_info_add b where a.form_id b.form_id)

    I can see no "=" sign in between a.form_id and b.form_id
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2007
    Posts
    48
    update application_xml_q2007 a
    set a.xml_name = (select b.xml_name from application_xml_info_add b where a.form_id = b.form_id);


    still got ORA-01427: single-row subquery returns more than one row

    I am not sure how to fix this...anyone? Thanks.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Problem here is ...

    (select b.xml_name from application_xml_info_add b where a.form_id = b.form_id)

    ... is returning more than one row meaning, more than one value for b.xml_name therefore Oracle doesn't know which one you want to use to update row on application_xml_q2007 table and cancels with ORA-01427

    You have to do something to force your inline query to return a single row.

    Question: Are all b.xml_name values the same for a specific form_id?

    If your answer is YES that means you don't care about which one is used to update your table therefore you can resort to something like...

    (select * from (select b.xml_name from application_xml_info_add b where a.form_id = b.form_id) where rownum < 2);

    If your answer is NO you have to find another way of forcing inline query to return single row... tip: work on WHERE clause.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Apr 2007
    Posts
    1

    Try distinct inside the sub query

    Try distinct inside the sub query

    update application_xml_q2007 a
    set a.xml_name = (select DISTINCT b.xml_name from application_xml_info_add b where a.form_id = b.form_id);

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    DISTINCT might return several distinct values, you want just one
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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