Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
   id        INTEGER  NOT NULL PRIMARY KEY 
  ,name      VARCHAR(6) NOT NULL
  ,parent_id INTEGER  NOT NULL
);
INSERT INTO mytable(id,name,parent_id) VALUES (1,'A',0);
INSERT INTO mytable(id,name,parent_id) VALUES (2,'B',1);
INSERT INTO mytable(id,name,parent_id) VALUES (3,'C',2);
INSERT INTO mytable(id,name,parent_id) VALUES (4,'D',1);
INSERT INTO mytable(id,name,parent_id) VALUES (5,'E',0);
INSERT INTO mytable(id,name,parent_id) VALUES (6,'F',0);

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

with recursive cte(id, parent_id) as (
    select id, parent_id from mytable where name = 'A'
    union all
    select t.id, t.parent_id from mytable t inner join cte c on c.id = t.parent_id
)
delete from mytable where id in (select id from cte)

DELETE 4

select * from mytable order by id

id
name
parent_id

5
E
0

6
F
0

SELECT 2