This discussion is archived
6 Replies Latest reply: Jan 4, 2013 1:27 AM by 884659 RSS

Need a select script how to update row from another row of same table

884659 Newbie
Currently Being Moderated
I have a table with the following columns and values
KeyID       EffectiveDate     InactiveDate
1              01/01/2013
1              01/02/2013
1              01/03/2013
2              12/31/2012
2              01/02/2013
2              01/03/2013
I need to update the InactiveDate of a row with the EffectiveDate of a row with the newer EffectiveDate than the row to be updated and with the same KeyID.
KeyID       EffectiveDate     InactiveDate
1              01/01/2013      01/02/2013
1              01/02/2013      01/03/2013
1              01/03/2013
2              12/31/2012      01/02/2013
2              01/02/2013      01/03/2013
2              01/03/2013
I want to have a select script which shows
KeyID       EffectiveDate        InactiveDate    New_InactiveDate
1              01/01/2013                              01/02/2013
1              01/02/2013                              01/03/2013
1              01/03/2013
2              12/31/2012                              01/02/2013
2              01/02/2013                              01/03/2013
2              01/03/2013
Thanks for any help.

Edited by: 881656 on Jan 3, 2013 5:57 PM

Edited by: 881656 on Jan 3, 2013 6:15 PM

Edited by: 881656 on Jan 3, 2013 6:20 PM
  • 1. Re: Need a select script for this scenario
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Need a select script how to update row from another row of same table
    SomeoneElse Guru
    Currently Being Moderated
    I need to update the InactiveDate of a row with the EffectiveDate
    ...
    I want to have a select script which shows
    Do you need an update or a select statement? (not sure what a select "script" is)

    You should also provide us with real working data, such as CREATE TABLE/INSERT statements or at least a WITH clause.
  • 3. Re: Need a select script how to update row from another row of same table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    881656 wrote:
    I have a table with the following columns and values
    KeyID       EffectiveDate     InactiveDate
    1              01/01/2013
    1              01/02/2013
    1              01/03/2013
    2              12/31/2012
    2              01/02/2013
    2              01/03/2013
    
    I need to update the InactiveDate of a row with the EffectiveDate of a row with the newer EffectiveDate than the row to be updated and with the same KeyID.
    
    KeyID       EffectiveDate     InactiveDate
    1              01/01/2013      01/02/2013
    1              01/02/2013      01/03/2013
    1              01/03/2013
    2              12/31/2012      01/02/2013
    2              01/02/2013      01/03/2013
    2              01/03/2013
    That sounds like a job for the analytic LEAD function:
    MERGE INTO table_x     dst
    USING (
         SELECT  keyid
         ,     effectivedate
         ,     LEAD (effectivedate) OVER ( PARTITION BY  keyid
                                           ORDER BY         activedate
                                         )            AS inactivedate
          )               src
    WHEN MATCHED THEN UPDATE
    ON    (    src.id          = dst.id
          AND  src.effectivedate     = dst.effectivedate
          )
    SET     dst.inactivedate     = src.inactivedate
    ;
    I want to have a select script which shows
    
    KeyID       EffectiveDate        InactiveDate    New_InactiveDate
    1              01/01/2013                              01/02/2013
    1              01/02/2013                              01/03/2013
    1              01/03/2013
    2              12/31/2012                              01/02/2013
    2              01/02/2013                              01/03/2013
    2              01/03/2013
    Once you've done the MERGE above, that's simply:
    SELECT    *
    FROM      table_x
    ORDER BY  keyid
    ,         effectivedate
    ;
    If you haven't done sthe MERGE, then see the query in the USING clause of the MERGE.


     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
    In the case of a DML operation (such as UPDATE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table after the DML.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 4. Re: Need a select script how to update row from another row of same table
    884659 Newbie
    Currently Being Moderated
    I want the Select script that will display the KeyID, EffectiveDate, InactiveDate, and New_InactiveDate.

    Thanks.
  • 5. Re: Need a select script how to update row from another row of same table
    sb92075 Guru
    Currently Being Moderated
    881656 wrote:
    I want the Select script that will display the KeyID, EffectiveDate, InactiveDate, and New_InactiveDate.

    Thanks.
    Handle:     881656
    Status Level:     Newbie
    Registered:     Aug 25, 2011
    Total Posts:     59
    Total Questions:     34 (33 unresolved)

    why do waste time here when you rarely get your questions answered?

    consider to actually post required details


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 6. Re: Need a select script how to update row from another row of same table
    884659 Newbie
    Currently Being Moderated
    Frank,

    The query in the USING clause is what I'm looking for.

    I'm using Oracle 11.2.0.1.0.

    Thanks,

    Peter

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points