6 Replies Latest reply: Jan 4, 2013 3:27 AM by 884659 RSS

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

    884659
      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
          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
            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
              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
                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
                  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
                    Frank,

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

                    I'm using Oracle 11.2.0.1.0.

                    Thanks,

                    Peter