clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2043087 fiddles created (16615 in the last week).

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;
id description parentid
1 Group1 0
2 Group2 0
11 Group1Sub1 1
21 Group2Sub1 2
211 Group2Sub211 21
212 Group2Sub212 21
 hidden batch(es)