clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36280 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)