question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked EchoLiu-msft edited

SQL Server How to delete all direct / indirect child data in chain when i know the top ParentID

Say I have a table like

ID ParentID Name


1 0 Test1
2 1 Test2
3 2 Test3
4 3 Test4
5 4 Test5
6 2 Test6
7 0 Test7
8 7 Test8

i know the ID of a parent whose ID is 1 and it has some direct & indirect child. now how could i remove all direct & indirect child in chain if i know the parent ID?

please guide me with sql.

Thanks


i know the

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
1 Vote"
TomCooper-6989 answered
 Declare @ParentToDelete int = 1;
 ;With cte As
 (Select ID From #Sample Where ID = @ParentToDelete
 Union All
 Select s.ID
 From #Sample s
 Inner Join cte c On s.ParentID = c.ID)
 Delete From s From #Sample s Where s.ID In (Select c.ID From cte c)

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered TomPhillips-1744 edited
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

Please refer to:
You can delete all their children based on the known id or parentid.

 CREATE TABLE #yourtable (ID int,ParentID int,[Name] varchar(15))
 INSERT INTO #yourtable VALUES(1,0,'Test1'),(2,1,'Test2'),
                              (3,2,'Test3'),(4,3,'Test4'),
                              (5,4,'Test5'),(6,2,'Test6'),
                              (7,0,'Test7'),(8,7,'Test8')
    
 ;WITH cte as
   (SELECT id,parentid,[name] FROM #yourtable
    WHERE parentid=1
    UNION ALL
    SELECT f.id,f.parentid,f.[name] FROM #yourtable f
    JOIN cte ON f.parentid=cte.id)
     
 DELETE FROM #yourtable
 WHERE id in(SELECT id FROM cte)

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.