How To Update Multiple Conditions Using MERGE Statements

  • I have to compare source and Target data and MERGE them accordingly. But I have multiple conditions.
    Below is my first set of source data

    Source-Table1
    ID   StartDate             EndDate    Designation
    1     2018-01-01        2199-12-31   Associate Engineer
    2     2018-02-01         2199-12-31     Software Engineer

    Target-Table2
    ID   StartDate             EndDate    Designation
    1     2018-01-01        2199-12-31   Associate Engineer
    2     2018-02-01         2199-12-31     Software Engineer

    Create table Table1(
    Id int,
    StartDate datetime,
    EndDate Datetime,
    Designation nvarchar(100)
    )

    Create table Table2(
    Id int,
    StartDate datetime,
    EndDate Datetime,
    Designation nvarchar(100)
    )

    --insert values
    insert into Table1 values(1,'2018-01-01','2199-12-31','Associate Engineer')
    insert into Table1 values(2,'2018-02-01','2199-12-31','Software Engineer')

    --Use Merge to Update/Insert values
    MERGE Table2 t
    USING Table1 s
    on t.id=s.id
    WHEN MATCHED THEN
    update SET
    t.id=s.id,
    t.StartDate=s.StartDate,
    t.EndDate=s.EndDate,
    t.Designation=s.Designation

    WHEN NOT MATCHED BY TARGET THEN
    INSERT(
    ID,
    StartDate,
    ENDDate,
    Designation
    )
    VALUES
    (s.ID,
    s.StartDate,
    s.EndDate,
    s.Designation)
    ;

    Now, this code doesnt work if I have to update multiple conditions ,for Example,if My source data has below values,then each ID cannot have overlap values.
    When there is new record for each ID, I have to check on StartDate, If start Date is < enddate then, update my old endDate to last day of the previous month and
    insert the new record else if ID and StartDate match, then update all records. If ID and StartDate does not mach then Insert.
    Source-Table1
    ID   StartDate  EndDate  Designation
    1   2018-01-01         2199-12-31 Associate Engineer
    1   2018-02-01         2199-12-31 Software Engineer
    2   2018-02-01         2199-12-31 Software Engineer
    2   2018-03-01         2199-12-31 Senior Software Engineer

    Now I want my Target Table to have
    ID   StartDate  EndDate  Designation
    1   2018-01-01         2018-01-31 Associate Engineer
    1   2018-02-01         2199-12-31 Software Engineer
    2   2018-02-01         2018-02-28 Software Engineer
    2   2018-03-01         2199-12-31 Senior Software Engineer

    I am not able to do this Using MERGE statements.Can anyone of you help me to get this result?

  • Hi,
    Looks like you are working with slowly changing dimensions.  An alternate way is to split the MERGE statement into two, INSERT and UPDATE separately.

    --Use Merge to Update/Insert values
    MERGE Table2 t
      USING Table1 s
      ON t.id=s.id
      WHEN MATCHED THEN
      UPDATE
      SET
        t.id=s.id,
        t.StartDate=s.StartDate,
        t.EndDate=s.EndDate,
        t.Designation=s.Designation;
      GO
     
      MERGE Table2 t
      USING Table1 s
      ON t.id=s.id  and t.startdate <> s.startdate
      WHEN NOT MATCHED BY TARGET THEN
      INSERT(
      ID,
      StartDate,
      ENDDate,
      Designation
      )
      VALUES
      (s.ID,
      s.StartDate,
      s.EndDate,
      s.Designation)  ;
      GO

    To expire the previous record, you can use the following SELECT query and convert it as a separate UPDATE statement. 

       SELECT T.ID, T.STARTDATE,  (CASE WHEN T.STARTDATE < X.MaxDate THEN x.MaxDate-1 ELSE T.ENDDATE END) AS ENDDATE, T.DESIGNATION
      FROM Table1 t
      left join (
      select Top 1 ID,  MAX(t1.startdate) MaxDate
      from Table1 t1
      Group by t1.ID
      Order by 1 desc
      )X
      on t.ID + 1 = x.ID

    =======================================================================

  • I think you would only need to match by ID from the target table. I assume that your target table would be having id as a unique field?. (Edit: i think you have id and startdate as a unique key)

    So the tricky part would be to write a query based on how you want the output rows should look like in the "USING" portion of the MERGE. After that what you need to do would be to match it on the ON clause using the ID field and the Start date. 

    Eg:


    MERGE
    INTO Table2 t
    USING (select x.id
                  ,x.startdate
                  ,case when dateadd(dd
                                         ,-1
                                         ,lead(x.startdate) over(partition by x.id order by x.startdate asc)
                                 ) is null then
                                /* Incase the lead of startdate is a null implies its the current open record with date (2199-12-31)*/
                                x.enddate
                         else dateadd(dd
                                         ,-1
                                         ,lead(x.startdate) over(partition by x.id order by x.startdate asc)
                                 )
                         end
                    as enddate
                    ,x.designation
             from Table1 x
            )s
    ON t.id=s.id
    AND t.startdate=s.startdate
    WHEN MATCHED THEN
    UPDATE
      SET t.EndDate=s.EndDate,
           t.Designation=s.Designation
     WHEN NOT MATCHED THEN        
     INSERT (id
                ,startdate
                ,enddate
                ,designation)    
     VALUES(s.id
             ,s.startdate
             ,s.enddate
             ,s.designation 
             )
    GO

  • Modify the ON to

    On t.id = s.id
    And t.startdate = s.startdate

    then add a check for

    When not matched by source
    Then
    Update
    Set enddate = eomonth(loaddate)

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Why don't you use case statement and subqueries in the set part instead?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply