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

    Unanswered: date update in system

    i am trying to update the end date in a coulmn based on the most recent start date.

    ie

    id Start date End date
    1123 11-02-2006
    1123 14-04-2005

    so i would want the last rows end date to be 10-02-2006 1 day less than the start date of the first row.
    but i dont want to update the first row as i want to leave it open

    code as follows
    UPDATE
    fsc.address_usages
    SET
    fsc.address_usages.aus_end_date = ( SELECT
    MAX(FSC_ADDRESS_USAGES_PARTIES.AUS_start_date)-1
    FROM
    FSC.ADDRESS_USAGES FSC_ADDRESS_USAGES_PARTIES,
    HOUSEHOLD_PERSONS,
    TENANCY_INSTANCES,
    FSC.PARTIES,
    TENANCIES
    WHERE
    ( TENANCIES.TCY_REFNO=TENANCY_INSTANCES.TIN_TCY_REFN O )
    AND ( TENANCY_INSTANCES.TIN_HOP_REFNO=HOUSEHOLD_PERSONS. HOP_REFNO )
    AND ( HOUSEHOLD_PERSONS.HOP_PAR_REFNO=FSC.PARTIES.PAR_RE FNO )
    AND ( FSC_ADDRESS_USAGES_PARTIES.AUS_PAR_REFNO=FSC.PARTI ES.PAR_REFNO )
    AND (
    FSC_ADDRESS_USAGES_PARTIES.AUS_AUT_FAR_CODE = 'CONTACT' )
    AND FSC_ADDRESS_USAGES_PARTIES.aus_end_date IS NULL

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    I do something similar and my solution was

    To step through each record in a cursor
    order that by the key field and the start date

    fetch into the cursor values set to variables
    fetch next then
    compare prior to next fetch
    the key if the same set enddate = startdate-1
    fetch next

    I would also suggest making end dates = to a date in the future for claritiy

    This will assure that you won't miss the middle record of 3.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I would personally avoid rbackmann's cursor-based solution because doing everything in a single SQL statement always results in better performance than cursor loops.

    Could you be clearer on what you call "the first row" ? Is it, among the rows candidate to the update, the row which has the max start_date, as it seem to appear in your solution ? BTW it seems like your query is not complete : I can't see where your subselect ends and I don't see the where clause of your UPDATE... Could you post the whole query ?

    As I see it, one solution would be like :

    Code:
    update table
    set end_date = (select max(start_date) - 1 from ...)
    where end_date is null
    and ...
    and start_date <> (select max(start_date) from ...);
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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