You can use
CTE[
^]:
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
(
SELECT id, description, parentid
FROM atable
WHERE id = 11
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[
^]