Results 1 to 7 of 7

Thread: Update/Merge

  1. #1
    Join Date
    Jul 2013
    Posts
    24

    Unanswered: Update/Merge

    I want to Update END_DATE values in Target table with Source table.
    Transformation: If Source.END_DATE='9099-12-31' AND for same record Target.END_DATE='9999-12-31' it is acceptable because in our environment both means same just different representations chosen. So we don't need to update such records in Target table.

    Except above case, I want to update END_DATE in Target Table if they don't match.

    I have created following SQL and it works but looking for better way to address this issue.

    Solution #1:
    -------------

    Works but even updates records that are not suppose to be updated (part of Transformation exception see above):
    ---------------------------------------------------
    MERGE into Target table p
    USING (

    SELECT
    t.SURR_KEY
    ,CASE
    WHEN END_DATE='2099-12-31' THEN '9999-12-31'
    ELSE END_DATE
    END AS END_DATE


    FROM SOURCE_TABLE

    INNER JOIN LOOKUP_TABLE_1 c
    ON KEY=c.KEY

    INNER JOIN LOOKUP_TABLE_2 m
    ON
    SRC_KEY_1 = m.KEY
    and m.KEY = c.KEY

    INNER JOIN TARGET_TABLE t
    ON SRC_KEY_1=t.TGT_KEY_1
    AND SRC_KEY_2 = t..TGT_KEY_2
    AND SRC_KEY_3 = t..TGT_KEY_3
    AND SRC_KEY_4 = t..TGT_KEY_4
    AND c.KEY = t.KEY
    AND SRC_KEY_5 =t..TGT_KEY_5
    AND END_DATE <> t.END_DATE)s

    ON p.SURR_KEY = s.SURR_KEY
    WHEN MATCHED THEN UPDATE
    SET END_DATE = s.END_DATE
    ,LAST_UPD_TMSP = CURRENT TIMESTAMP


    Solution #2:
    ------------

    WORKS but longer than Solution #1:

    Step 1:
    Create temp table and store the output from join mentioned in Solution # 1, Except TARGET TABLE, and transform END_DATE values from '2099-12-31' To '9999-12-31' so that we can avoid updating irrelevant records.

    created index and run stats on Temp table before running Step 2.

    Step 2:
    Using Merge, Update Target table based on Join between Temp table and the Target table.
    Last edited by zaino22; 01-22-14 at 10:21.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please post exact query.
    If you want to modify table/column names(e.g. because of security reason),
    please be careful to avoid misspells or simple syntax errors.

    MERGE into Target table p
    USING (
    Shouldn't it be Target_table?

    FROM SOURCE_TABLE
    TPGMBLRL
    Why did you add an alias TPGMBLRL for SOURCE_TABLE?
    The alias TPGMBLRL was never used.

    AND SRC_KEY_2 = t..TGT_KEY_2
    Didn't you got error from "t..TGT_KEY_2"?


    INNER JOIN TARGET_TABLE t
    ON SRC_KEY_1=t.TGT_KEY_1
    AND SRC_KEY_2 = t..TGT_KEY_2
    AND SRC_KEY_3 = t..TGT_KEY_3
    AND SRC_KEY_4 = t..TGT_KEY_4
    AND c.KEY = t.KEY
    AND SRC_KEY_5 =t..TGT_KEY_5
    AND END_DATE <> t.END_DATE
    I thought that it might be unnecessary to join TARGET_TABLE.
    How about to try these ON conditions to move out to ON condition between source_table and target_table
    (in other words, replace them with "ON p.SURR_KEY = s.SURR_KEY").

  3. #3
    Join Date
    Jul 2013
    Posts
    24
    Below is what I could manage to build after your eyes spotted some silly mistakes....sorry about that!
    If i understand you correctly, you are suggesting to remove the join with Target table within parenthesis? if we do, since Source does not have the Surrogate key column that we have in Target table there is nothing to join on.
    By having the Target table in the parentheiss with surrogate key available, and by using surrogate key using as join condition outside of bracket, we have something to join onto...
    I am sure it is my fault if i have not been able to convey the problem correctly.

    MERGE into TARGET_TABLE p
    USING (

    SELECT
    t.SURR_KEY
    ,CASE
    WHEN END_DATE='2099-12-31' THEN '9999-12-31'
    ELSE END_DATE
    END AS END_DATE

    FROM SOURCE_TABLE
    INNER JOIN
    TBB c
    ON STUB_CD=c.TUB_CD

    INNER JOIN
    PROGRAMMING m
    ON
    PROG_CD = m.PGM_CCD
    and m.TUB_CD = c.TUB_CD

    INNER JOIN TARGET_TABLE t
    ON PROG_CD=t.tPGM_CCD
    AND ID_CD = t.tID_CD
    AND SEQ_NUM = t.tSEQ_NUM
    AND SRC_CD = t.tSRC_CD
    AND c.TUB_CD = t.tTUB_CD
    AND INITIAL_DATE =t.tSTART_DT
    AND END_DATE <> t.tEND_DT )s

    ON p.SURR_KEY = s.SURR_KEY
    WHEN MATCHED THEN UPDATE
    SET p.tEND_DATE = s.END_DATE
    ,LAST_UPD_TMSP = CURRENT TIMESTAMP


    --------------

    is below what you are suggesting?

    MERGE into TARGET_TABLE p
    USING (

    SELECT
    t.SURR_KEY
    ,CASE
    WHEN END_DATE='2099-12-31' THEN '9999-12-31'
    ELSE END_DATE
    END AS END_DATE

    FROM SOURCE_TABLE
    INNER JOIN
    TBB c
    ON STUB_CD=c.TUB_CD

    INNER JOIN
    PROGRAMMING m
    ON
    PROG_CD = m.PGM_CCD
    and m.TUB_CD = c.TUB_CD )s

    ON p.SURR_KEY = s.SURR_KEY
    WHEN MATCHED THEN UPDATE
    SET p.tEND_DATE = s.END_DATE
    ,LAST_UPD_TMSP = CURRENT TIMESTAMP

    Source table:
    Case #1: IF END_DATE in Source table is '9099-12-31' and for the same record (by using Join condition above we can determine if it same record or not) it is '9999-12-31' in Target table then we should NOT update this in Target table as this is OK.
    Case #2: IF END_DATE in Source table is '2014-10-15' and for the same record (by using Join condition above we can determine if it same record or not) it is '2013-05-25' in Target table then we should UPDATE this in Target table as this is NOT OK.

    I realize Solution #1 updates both Case #1, and Case #2 because this logic see '9099-12-31' in Source table is different than '9999-12-31'.
    Solution #2 however transforms Case #1 in Temp table to '9999-12-31', making Case #1 non updatable.

    I was wondering if there was a shorter approach than the Solution #2. Sorry for the mess up before, and I hope you are still willing to help.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    ...
    ...

    I thought that it might be unnecessary to join TARGET_TABLE.
    How about to try these ON conditions to move out to ON condition between source_table and target_table
    (in other words, replace them with "ON p.SURR_KEY = s.SURR_KEY").
    My intended suggestion was to replace "ON p.SURR_KEY = s.SURR_KEY outside the parentheses"
    with "ON conditions related to TARGET_TABLE t inside the parentheses".

    An example might be the following.

    Note1: Naturally, it is required to add some columns in select-list
    and to make ammendments for some qualifiers of columns in ON conditions.

    Note2: You wrote
    ...
    ON PROG_CD=t.tPGM_CCD
    AND ID_CD = t.tID_CD
    AND SEQ_NUM = t.tSEQ_NUM
    AND SRC_CD = t.tSRC_CD
    AND c.TUB_CD = t.tTUB_CD
    AND INITIAL_DATE =t.tSTART_DT
    AND END_DATE <> t.tEND_DT )s
    ON p.SURR_KEY = s.SURR_KEY
    WHEN MATCHED THEN UPDATE
    SET p.tEND_DATE = s.END_DATE
    ,LAST_UPD_TMSP = CURRENT TIMESTAMP
    But, I assumed tEND_DT might be tEND_DATE, because of your descriptions.

    Example 1:
    Code:
    MERGE INTO
          target_table p
    USING (SELECT PROG_CD
                , ID_CD
                , SEQ_NUM
                , SRC_CD
                , c.TUB_CD
                , INITIAL_DATE
                , CASE
                  WHEN END_DATE = '2099-12-31' THEN
                       '9999-12-31'
                  ELSE END_DATE
                  END  AS END_DATE
              /*
                , COALESCE(NULLIF(end_date , '2099-12-31') , '9999-12-31') AS end_date
              */
            FROM  SOURCE_TABLE 
            INNER JOIN 
                  TBB c
             ON   c.TUB_CD = STUB_CD
            INNER JOIN 
                  PROGRAMMING m
             ON   m.PGM_CCD = PROG_CD
              and m.TUB_CD  = c.TUB_CD
          ) s
     ON   p.tPGM_CCD  = s.PROG_CD
      AND p.tID_CD    = s.ID_CD
      AND p.tSEQ_NUM  = s.SEQ_NUM
      AND p.tSRC_CD   = s.SRC_CD
      AND p.tTUB_CD   = s.TUB_CD
      AND p.tSTART_DT = s.INITIAL_DATE
      AND p.tEND_DATE <> s.END_DATE
    
    WHEN MATCHED THEN
    UPDATE
     SET   tEND_DATE     = s.END_DATE 
         , LAST_UPD_TMSP = CURRENT TIMESTAMP
    ;

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I was going to offer advice similar to Tonkuma and as you pointed out the problem in your last update as well. You are not equating 9099 to 9999.

    AND END_DATE <> t.tEND_DT )s
    You could even use the same case statement that you used in your select to compare to target end date.

  6. #6
    Join Date
    Jul 2013
    Posts
    24
    tonkuma you mumma must love you... and wud be proud ov ya! thank you as always very helpful and good eyes for details


    Quote Originally Posted by tonkuma View Post
    My intended suggestion was to replace "ON p.SURR_KEY = s.SURR_KEY outside the parentheses"
    with "ON conditions related to TARGET_TABLE t inside the parentheses".

    An example might be the following.

    Note1: Naturally, it is required to add some columns in select-list
    and to make ammendments for some qualifiers of columns in ON conditions.

    Note2: You wrote

    But, I assumed tEND_DT might be tEND_DATE, because of your descriptions.

    Example 1:
    Code:
    MERGE INTO
          target_table p
    USING (SELECT PROG_CD
                , ID_CD
                , SEQ_NUM
                , SRC_CD
                , c.TUB_CD
                , INITIAL_DATE
                , CASE
                  WHEN END_DATE = '2099-12-31' THEN
                       '9999-12-31'
                  ELSE END_DATE
                  END  AS END_DATE
              /*
                , COALESCE(NULLIF(end_date , '2099-12-31') , '9999-12-31') AS end_date
              */
            FROM  SOURCE_TABLE 
            INNER JOIN 
                  TBB c
             ON   c.TUB_CD = STUB_CD
            INNER JOIN 
                  PROGRAMMING m
             ON   m.PGM_CCD = PROG_CD
              and m.TUB_CD  = c.TUB_CD
          ) s
     ON   p.tPGM_CCD  = s.PROG_CD
      AND p.tID_CD    = s.ID_CD
      AND p.tSEQ_NUM  = s.SEQ_NUM
      AND p.tSRC_CD   = s.SRC_CD
      AND p.tTUB_CD   = s.TUB_CD
      AND p.tSTART_DT = s.INITIAL_DATE
      AND p.tEND_DATE <> s.END_DATE
    
    WHEN MATCHED THEN
    UPDATE
     SET   tEND_DATE     = s.END_DATE 
         , LAST_UPD_TMSP = CURRENT TIMESTAMP
    ;

  7. #7
    Join Date
    Jul 2013
    Posts
    24
    hi dav1mo.. thanks for your input as well. Could you please write in code what you mean by following?

    "You could even use the same case statement that you used in your select to compare to target end date."



    Quote Originally Posted by dav1mo View Post
    I was going to offer advice similar to Tonkuma and as you pointed out the problem in your last update as well. You are not equating 9099 to 9999.



    You could even use the same case statement that you used in your select to compare to target end date.

Posting Permissions

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