I need to merge 2 tables using the merge command , But also I need to delete some row from the target table
so the logic of the merge should be
1. If rows is located in both source and target tables , Then update
2. If rows is locted in source table and not in target table then Insert
3. If rows is located in target and not in source Then delete from target
You cannot do all 3 items above using a single MERGE command. This will insert (#2) or "update or delete" (#1) (#3).
You need two statements.
- a MERGE and a DELETE.
- two MERGE commands, one does the insert and update, the other does the delete.
Sorry, I've just realised that my previous post is incorrect.
For the original requirement you cannot use MERGE to do the delete. The merge delete option is only for 'matched rows' and the requirement stated originally is for 'unmatched rows'.
To clarify, to do this you need two statements, typically a merge to do #1 and #2 and a delete to do #3.