Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2005
    Posts
    24

    Unanswered: update.... cant do it please help!

    Hi... i just dont seem to be able to get the hang on this.. it says single row sub query returns more than one row??

    can some one explain what i keep doing wrong?

    UPDATE
    hb_history hi1

    SET hi1.hhi_award_to_date = (SELECT
    hi.HHI_AWARD_FROM_DATE + 6
    FROM
    HB_HISTORY hi
    WHERE
    hi.hhi_award_to_date IS NULL)

    WHERE
    hi1.hhi_award_to_date IS NULL

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Apparently there is more than one row in HB_HISTORY with a null hhi_award_to_date, so Oracle doesn't know which one's hhi_award_from_date you want to use. Change the query so that it returns a single row.

  3. #3
    Join Date
    Oct 2005
    Posts
    24
    UPDATE
    HB_HISTORY hi1

    SET hhi_award_to_date = (SELECT
    DISTINCT hi.HHI_AWARD_FROM_DATE + 6
    FROM
    HB_HISTORY hi
    WHERE
    hi.hhi_award_to_date IS NULL
    AND
    hi1.hhi_rac_accno= hi.hhi_rac_accno
    AND
    hi1.hhi_award_from_date = hi.hhi_award_from_date
    AND
    hi1.hhi_source = hi.hhi_source)
    WHERE
    hi1.hhi_award_to_date IS NULL

    i put this and it appears to of worked?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It works if and only if all the rows where to_date is null have the same from_date. Is that sure to be the case, or did you just get lucky this time?

  5. #5
    Join Date
    Oct 2005
    Posts
    24

    Red face

    no they may have different from dates? and it still worked...

    oh no you got me worried n ow!
    Last edited by dan_mason; 10-24-05 at 08:01.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm not sure what you really want to do here, but maybe it is this?:

    UPDATE hb_history
    SET hhi_award_to_date = HHI_AWARD_FROM_DATE + 6
    WHERE hhi_award_to_date IS NULL;

  7. #7
    Join Date
    Oct 2005
    Posts
    24
    will that do any record in hb history table?

    each record has a id number a from date and a too date

    will that make the to date 6 days on from the start date relevant to the id?
    baring in mind that there can be multiple from dates for an Id?


    eg

    ID from date to date
    10 10/10/2005
    10 11/11/2005


    would become

    ID from date to date
    10 10/10/2005 16/10/2005
    10 11/11/2005 17/11/2005

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, it updates each row's null end date based on the start date of that row.

  9. #9
    Join Date
    Oct 2005
    Posts
    24
    so mine worked in that way aswell?

    have you any advice on how to get my head round these inserts and updates statements?

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, yours used the from_date from all rows where the to_date was null, not just this row, hence you got the error.

    This DBAZine Article might be of interest. Apart from that, I can only suggest reading a book on SQL (but I can't recommend any myself).

Posting Permissions

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