Click here to Skip to main content
14,928,057 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have table like below.


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


Thanks in Advance

What I have tried:

SQL
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;


SQL
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;
Posted
Updated 6-Mar-21 3:08am
v2

1 solution

You can use CTE[^]:

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

insert into atable values (1, 'Group1', 0), (2, 'Group2', 0),
(11, 'Group1Sub1', 1), (111, 'Group1Sub111', 11),
(112, 'Group1Sub112', 11), (1111, 'Group1Sub1111', 111),
(1112, 'Group1Sub1112', 111), (1121, 'Group1Sub1121', 112),
(21, 'Group2Sub1', 2), (211, 'Group2Sub211', 21),
(212, 'Group2Sub212', 21);


;WITH CTE AS
(
  --initial part
  SELECT id, description, parentid 
  FROM atable
  WHERE id = 11
  --recursive part
  UNION ALL
  SELECT a.id, a.description, a.parentid
  FROM atable a INNER JOIN CTE c ON a.parentid = c.id
)
DELETE aa
FROM atable aa INNER JOIN CTE c ON aa.parentid = c.id;

SELECT  *
FROM atable;


SQL Server 2019 | db<>fiddle[^]

For further details, please see:
Mastering Common Table Expression or CTE in SQL Server[^]
CP articles about CTE[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900