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

    Unanswered: UPDATE row based on a row in same table

    Hi,

    i have a table that is setup like the following

    application_id code_id Register_date re_register date
    12 HR 01-01-2005 01-01-2006
    12 CT 01-09-2005 10-12-2005
    12 CR 01-09-2009 01-05-2009

    ho would i update all applications where the reg and re reg date for CT or CR is not the same as HR and set them to the same as hr?

    cheers
    dan

    this is what i have upto now

    UPDATE
    applic_list_entries
    SET ale_registered_date = (SELECT
    app1.ale_registered_date
    FROM
    applic_list_entries app1,
    applic_list_entries app2
    WHERE
    app1.ale_rli_code = 'HR'
    AND
    app2. ale_rli_code = 'CR'
    AND
    app1.ale_app_refno = app2.ale_app_refno)
    WHERE
    ale_app_refno = (SELECT
    app1.ale_app_refno
    FROM
    applic_list_entries app1,
    applic_list_entries app2
    WHERE
    app1.ale_rli_code = 'HR'
    AND
    app2.ale_rli_code = 'CR'
    AND
    app1.ale_app_refno = app2.ale_app_refno)
    Last edited by dan_mason; 10-12-05 at 08:42.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try this:
    Code:
    UPDATE applic_list_entries app2
    SET ale_registered_date = (SELECT app1.ale_registered_date
                               FROM applic_list_entries app1
                               WHERE app1.ale_rli_code = 'HR'
                               AND app1.ale_app_refno = app2.ale_app_refno)
    WHERE app2.ale_rli_code IN ('CR','CT')
    AND EXISTS (SELECT NULL
                FROM applic_list_entries app1
                WHERE app1.ale_rli_code = 'HR'
                AND app1.ale_app_refno = app2.ale_app_refno);
    The "exists" is to ensure that you don't set the date to NULL on CR/CT rows where there is no matching HR. You can omit it if you want that to happen.

  3. #3
    Join Date
    Oct 2005
    Posts
    24
    thanks!! your number one guy!

    i feel bad postin sooo many questions... but go easy on me !


    i just cant seem to get my head around multiple updates / inserts..

Posting Permissions

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