sqlsql-serversql-server-2008-r2merge-statement

Update when relevant rows are not present in the source table


I have a merge statement like this:

MERGE DESTINATION
USING SOURCE WHERE <Some_Conditions>
WHEN MATCHED AND <Some_Conditions> THEN DELETE
WHEN MATCHED UPDATE
WHEN NOT MATCHED INSERT 

This is working fine but I have one more condition i.e. I have to update the rows in destination when their entry is not present in the source.

Ex.

Source

Column1 Column2 Column3
-----------------------
A        A       A
B        B       B

Destination

Column1 Column2 Column3
-----------------------------------
B        B       <Some_Calculation>
D        D       <Some_Calculation>

Now, as there are no rows in source for D, I have to modify Column 3 in destination with some calculation. But as merge is giving only the three options of delete, update and insert when rows matched or not.

How can I implement this functionality in the above statement?

Edit

Editing question with my comments below:

In the above example which I have given above is running then It is updating B and inserting A into destination table. But I want to update D also even when it is not present in the source table


Solution

  • Maybe read the documentation for MERGE:

    MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    ...
    

    WHEN NOT MATCHED BY SOURCE is what you need