CODEWITHSUNDEEP
sqlsql-servertriggerssql-deletecascading-deletes
Gokuh
Gokuh

Reputation: 1

Sql delete parent from child/delete whole tree

Delete parent and child in loop

Table 1 (Parent table)

Id  int

Table 2 (Relationship table)

Id1 int FOREIGN KEY (Id1) REFERENCES Table1 (Id)    
Id2 int FOREIGN KEY (Id2) REFERENCES Table1 (Id)

Id - Id1 one to one or one to zero relationship 
Id - Id2 one to many

Data in table 1

Id
1
2
3
4
5
6
7
8
9
10

Data in table 2

Id1 Id2
2   1
3   1
4   2
5   2
6   4
7   4
8   5
9   5

So it is like a tree with root as 1

1 has two childs 2 and 3
2 has two childs 4 and 5
4 has two childs 6 and 7
5 has two childs  8 and 9
3,6,7,8,9,10 has no child

Best possible way to achieve the below mentioned case:

Deleting 1 => deletes the complete table2 and table1(except 10 in table 1)

Upvotes: 0

Views: 1149

Answers (2)

GrApDev
GrApDev

Reputation: 150

Try

update table2 set id2 = null;
delete from table1 where id <> 10;
delete from table2;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

You can do this using Recursive CTE 

;WITH cte
     AS (SELECT Id1,
                Id2,
                id2 AS parent
         FROM   Yourtable
         UNION ALL
         SELECT a.Id1,
                a.Id2,
                b.Id2
         FROM   cte a
                JOIN Yourtable b
                  ON a.parent = b.id1)
SELECT *
FROM   cte
WHERE  parent = 1
OPTION (maxrecursion 0)
--DELETE FROM Yourtable
--WHERE  id1 IN (SELECT id1
--             FROM   cte
--             WHERE  parent = 1)
--OPTION (maxrecursion 0)

If the select is returning expected results then comment the select and un-comment the Delete

Upvotes: 0

Related Questions