3 Replies Latest reply on Jun 29, 2013 4:50 PM by Activesometimes-Oracle

    Merging and Updating Rows

    RamGrandhi

      I have a table structure as follows

       

      Student(Id,First_Name, Last_Name, email, Contact, Address1,Address2,City,Edit_Date,Create_Date,Archived)

       

      Now if there is more than one row with same email the one with the latest edit date should be updated with missing fields by using same field value other rows (if the field is present in more than one row, the one with the next latest edit date is to be considered) and the archived status of all rows with same email except this master row must be set to 1.

      The Create_Date must be set to the minimum of all the create_date values of rows with same email value

       

      The create table would be as follows:

      CREATE TABLE student(

      Id NUMBER PRIMARY KEY,

      first_name VARCHAR2(30) NOT NULL,

      last_name VARCHAR2(30) NOT NULL,

      email VARCHAR2(30) NOT NULL,

      contact NUMBER,

      adress1 VARCHAR(30),

      adress2 VARCHAR(30),

      city VARCHAR(30),

      edit_date DATE,

      create_date DATE,

      archived CHAR(1))

       

      Sample insert statements would be:

       

      insert into student values(1,'fname1','lname1','abc1@xyz.com',NULL,'Abc Street',NULL,'city1','22-Jun-13','20-Mar-10',null)

      insert into student values(2,'fname1','lname1','abc1@xyz.com',null,'Xyz Street','LN Road','city1','10-May-2013','20-Jun-06',null)

      insert into student values(3,'fname1','lname1','abc1@xyz.com',2224567890,'Abc Street','PQ Road',null,'05-Aug-12','05-06-11',null)

      insert into student values(4,'fname2','lname2','abc2@xyz.com',8888888888,'address2','address22',null,'07-May-12','25-Jan-12',null)

      insert into student values(5,'fname2','lname2','abc2@xyz.com',8888888888,null,null,'city2','17-Mar-13','17-Mar-91',null)

       

      The initial table would be as:

      Id

      First_Name

      Last_Name

      Email

      Contact

      Address1

      Address2

      City

      Edit_Date

      Create_Date

      Archived

      1

      Fname1

      Lname1

      abc1@xyz.com

      NULL

      Abc Street

      NULL

      City1

      22-Jun-13

      20-Mar-10

      NULL

      2

      Fname1

      Lname1

      abc1@xyz.com

      NULL

      Xyz Street

      LN Road

      City1

      10-May-13

      20-Jun-06

      NULL

      3

      Fname1

      Lname1

      abc1@xyz.com

      2224567890

      Abc Street

      PQ Road

      NULL

      05-Aug-12

      05-Jun-11

      NULL

      4

      Fname2

      Lname2

      abc2@xyz.com

      8888888888

      Address2

      Address22

      NULL

      07-May-12

      25-Jan-12

      NULL

      5

      Fname2

      Lname2

      abc2@xyz.com

      8888888888

      NULL

      NULL

      City2

      17-Mar-13

      17-Mar-91

      NULL

       

      The output needs to be like this:

      Id

      First_Name

      Last_Name

      Email

      Contact

      Address1

      Address2

      City

      Edit_Date

      Create_Date

      Archived

      1

      Fname1

      Lname1

      abc1@xyz.com

      2224567890

      Abc Street

      LN Road

      City1

      22-Jun-13

      20-Jun-06

      NULL

      2

      Fname1

      Lname1

      abc1@xyz.com

      NULL

      Xyz Street

      LN Road

      City1

      10-May-13

      20-Jun-06

      1

      3

      Fname1

      Lname1

      abc1@xyz.com

      2224567890

      Abc Street

      PQ Road

      NULL

      05-Aug-12

      05-Jun-11

      1

      4

      Fname2

      Lname2

      abc2@xyz.com

      8888888888

      Address2

      Address22

      NULL

      07-May-12

      25-Jan-12

      1

      5

      Fname2

      Lname2

      abc2@xyz.com

      8888888888

      Address2

      Address22

      City2

      17-Mar-13

      17-Mar-91

      NULL

       

      Please let me know how to get the desired result

       

      Thanks in advance

        • 1. Re: Merging and Updating Rows
          Frank Kulash

          Hi,

           

          Here's one way to do that:

           

          MERGE INTO  student dst

          USING (

                    SELECT  id

                    ,       LAST_VALUE (contact IGNORE NULLS)

                                OVER ( PARTITION BY  email

                                       ORDER BY      edit_date

                                     )  AS contact

          --            ...

                   ,       LAST_VALUE (create_date IGNORE NULLS)

                               OVER ( PARTITION BY  email

                                      ORDER BY      edit_date

                                    )  AS create_date

                   ,       CASE

                               WHEN  ROW_NUMBER ()   OVER ( PARTITION BY  email

                                                            ORDER BY      edit_date  DESC

                                                          ) = 1

                               THEN  NULL

                               ELSE  1

                           END  AS archived

                    FROM   student

                )   src

          ON    ( dst.id   = src.id )

          WHEN MATCHED THEN UPDATE

          SET     dst.contact     = NVL2 (src.archived, dst.contact,     src.contact)

          -- ...

          ,       dst.create_date = NVL2 (src.archived, dst.create_date, src.create_date)

          ,       dst.archived    = src.archived

          ;

           

          I only included a couple of the columns that can be NULL.  Address1, address2, city and edit_date (if edit_date really can be NULL) can be handled the same way that contact and create_date  are handled above.

           

           

          Don't try to insert VARCHAR2 values, such as '22-Jun-13', into DATE columns, such as create_date and edit_date.

           

           

          In the results you posted, you said that create_date for id=1 should be changed, even though it was not NULL.  Was that just a mistake?

          1 person found this helpful
          • 2. Re: Merging and Updating Rows
            RamGrandhi

            Thanks for the reply.

             

            Create date is the date on which the details of the student are entered into database for that particular row. So if there is more than one entry for that student, the first entries create date should be taken as the date on which it was created.

            • 3. Re: Merging and Updating Rows
              Activesometimes-Oracle

              As per this discussion it seems that, Student registration can have versions.. Table could have version_creation_date, Registered_date.

              Registered_date will be first date that student registers.. Later updates can be created as new versions of the same registration. We need to update earlier versions missing fields with the latest information... Guessing if this is the requirement.. ! , if not please ignore..!!