none
merge function

    Question

  • Hi friends,

    I have facing some issue with this merge function,

    scenario:

    I used two condition in merge function like

    1.When matched then update

    2.when not matched then insert.

    But with this conditions I couldn't achieve my goal, My goal is to update only changed rows , but with my scenario it is updating all the rows even the rows has no change.

    Any help is appreciated.

    Thanks.

    Tuesday, October 29, 2013 6:04 PM

Answers

  • Hi,

    You can use WHEN MATCHED AND (<Compare first Column> OR <Compare second Column>).

    Even with this approach you will end up updating all the column even if one column is changed.

    But you will not update if all the columns are matched.



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    • Edited by Vinay Valeti Tuesday, October 29, 2013 6:16 PM
    • Marked as answer by Murali dhar Tuesday, October 29, 2013 6:48 PM
    Tuesday, October 29, 2013 6:15 PM

All replies

  • Hi Murali,

    Check the conditions in ON clause.

    Thanks, Madhu

    Tuesday, October 29, 2013 6:07 PM
  • Thanks for the reply, I'm checking on primary key column,

    ON ( TGT.ChildID = SRC.ChildID )
    WHEN MATCHED THEN UPDATE

    ......

    WHEN NOT MATCHED THEN INSERT

    ......

    My agenda is when a childid is present it has to check whether there is a change in other fileds of that childid and it has to update only if there is a change, and if the childld wont exists it should insert.

    But my process is checking for childid and if it is present it is updating all fields irrespective of change.. I just want to know how to keep that condition that 'updates only if there is a change'

    Thanks.

    Tuesday, October 29, 2013 6:12 PM
  • Hi,

    You can use WHEN MATCHED AND (<Compare first Column> OR <Compare second Column>).

    Even with this approach you will end up updating all the column even if one column is changed.

    But you will not update if all the columns are matched.



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    • Edited by Vinay Valeti Tuesday, October 29, 2013 6:16 PM
    • Marked as answer by Murali dhar Tuesday, October 29, 2013 6:48 PM
    Tuesday, October 29, 2013 6:15 PM
  • Can you try to implement your code like below:

    MERGE Table1 AS T1
    USING Table2 AS T2
    ON (	T1.Col1= T2.Col1) 
    
    WHEN NOT MATCHED BY TARGET
        THEN INSERT(Col1, Col2, Col3, ... ColN) 
    		 VALUES(T2.Col1, T2.Col2, T2.Col3, .... T2.ColN)
    WHEN MATCHED 
        THEN UPDATE SET  T1.Col2 = T2.Col2
    					,T1.Col3 = T2.Col3
    					,T1.Col4 = T2.Col4
    					.....
    					....
    					,T1.ColN = T2.ColN

    
    Thanks, Madhu
    Tuesday, October 29, 2013 6:20 PM
  • Hi,

    You can use WHEN MATCHED AND (<Compare first Column> OR <Compare second Column>).

    Even with this approach you will end up updating all the column even if one column is changed.

    But you will not update if all the columns are matched.



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer



    Thanks a lot sir.
    Tuesday, October 29, 2013 6:48 PM