Delete sql table parent child details

Question

Hi,

I have table like below.

create table #table (id int, description nvarchar(50), parentid int);

insert into #table values (1, 'Group1', 0)
insert into #table values (2, 'Group2', 0)

insert into #table values (11, 'Group1Sub1', 1)
insert into #table values (111, 'Group1Sub111', 11)
insert into #table values (112, 'Group1Sub112', 11)
insert into #table values (1111, 'Group1Sub1111', 111)
insert into #table values (1112, 'Group1Sub1112', 111)
insert into #table values (1121, 'Group1Sub1121', 112)

insert into #table values (21, 'Group2Sub1', 2)
insert into #table values (211, 'Group2Sub211', 21)
insert into #table values (212, 'Group2Sub212', 21)

select * from #table;
drop table #table;

I need to delete only the first group child items except first group first child.
Am looking the result like below table.

create table #resulttable (id int, description nvarchar(50), parentid int);

insert into #resulttable values (1, 'Group1', 0)
insert into #resulttable values (2, 'Group2', 0)

insert into #resulttable values (11, 'Group1Sub1', 1)

insert into #resulttable values (21, 'Group2Sub1', 2)
insert into #resulttable values (211, 'Group2Sub211', 21)
insert into #resulttable values (212, 'Group2Sub212', 21)

select * from #resulttable;
drop table #resulttable;

Thanks in Advance

Answer 1

You will need to find out how you define what is the "first group". Here I've just hard-coded it in a variable.

DECLARE @firstgroup nvarchar(50) = 'Group1'

; WITH recurs AS (
   SELECT id, lvl = 1
   FROM   #table
   WHERE  description = @firstgroup
   UNION ALL
   SELECT t.id, r.lvl + 1
   FROM   recurs r
   JOIN   #table t ON t.parentid = r.id
)
DELETE #table 
FROM   #table t
WHERE  EXISTS (SELECT *
               FROM   recurs r
               WHERE  r.id = t.id
                 AND  r.lvl > 2)