delete rows using Merge

General

delete rows using Merge

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

3 REPLIES
Enthusiast

Re: delete rows using Merge

Searching for the same , BUt can not find a solution.

 

did you find a way ?

Senior Apprentice

Re: delete rows using Merge

Hi,

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.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: delete rows using Merge

Hi,
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.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com