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
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