delete values from depending tables using merge

I am trying to match rows from a table in source and destination db tables. I am able to delete that row which is present in dest table and not present in source table. However I also need to delete its dependent rows in other tables inside the dest db. Is there a way I can achieve this inside a merge.

Merge statement goes like this below.

MERGE Table1 as [Target]

using (select  A,B,C,D,E,
F,G,H,I,J,K,L
from Source.dbo.Table1 as al
where al.H is null) AS [Source]

ON ([Target].A = [Source].B)

WHEN NOT MATCHED By TARGET THEN
    INSERT(C,D,E,F,G)
    VALUES([Source].C,[Source].D,[Source].E,[Source].F,[Source].G);
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

See illustration below.

 Source
DB0.dbo.Table1

ID  IDID    NAME
1   6   xyz
2   3   yzx
3   4   abc
4   5   lym
5   1   tes


Dest
 Table1
ID  IDID    Name
1   6   xyz
2   3   yzx
3   4   abc
4   5   lym
5   1   tes
6   2   ads

Dependent Table
 DB1.Dbo,Table2
IDID    VALUE
1   BST
2   PL
3   NO
4   SS
5   DR
6   CR
7   LM

Thanks in advance.

Answer

Use a cascading deletion as Nenad Zivkovic suggested.

Here are a couple relevant links that provide some potential answers:

How to delete from source using merge command

Delete rows from multiple tables using a single sql query

Advertisement

Answer