How to delete all dependent rows
If you're not allowed to modify the tables, you can take a look at the EXISTS
operator.
It will allow you to delete rows from a table only if the query inside the EXISTS
return at least 1 result. You can use it to check dependencies.
You can write 3 queries:
DELETE C c
WHERE EXISTS (SELECT 1
FROM B b
WHERE c.b = b.b
AND EXISTS (SELECT 1
FROM A a
WHERE a.a = b.a
AND ... ));
DELETE B b
WHERE EXISTS (SELECT 1
FROM A a
WHERE a.a = b.a
AND ...);
DELETE A a
WHERE ...
The first one will take care of record in C
that references records in B
that references records in A
that you want to delete.
Then you can remove records from B
since there is no more dependencies in C
.
Finally, you're able to delete records from A
using the same logic.
You Can Use Cascade Cascading Referential Integrity Constraints
Update: you should enable Cascading Referential Integrity Constraints of Table A (PK) from Table B where ID of A is foreign Key and similarly of PK of Table B From Table C Where ID of B is foreign Key
MSDN LIBRARY
CODE PROJECT ARTICALE
Very Nice Article BLOG.SQL AUTHORITY