SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE treepaths (
    tpa_ance INTEGER NOT NULL, -- ancestor
    tpa_desc INTEGER NOT NULL, -- descendant
    tpa_leng INTEGER NOT NULL, -- lenght
    UNIQUE(tpa_ance, tpa_desc)
);
INSERT INTO treepaths (`tpa_ance`, `tpa_desc`, `tpa_leng`) VALUES
  ('1', '1', '0'),
  ('1', '2', '1'),
  ('1', '3', '2'),
  ('1', '4', '3'),
  ('1', '5', '4'),
  ('1', '6', '5'),
  ('2', '2', '0'),
  ('2', '3', '1'),
  ('2', '4', '2'),
  ('2', '5', '3'),
  ('2', '6', '4'),
  ('3', '3', '0'),
  ('3', '4', '1'),
  ('3', '5', '2'),
  ('3', '6', '3'),
  ('4', '4', '0'),
  ('4', '5', '1'),
  ('4', '6', '2'),
  ('5', '5', '0'),
  ('5', '6', '1'),
  ('6', '6', '0');
WITH cte AS (SELECT tpa_desc FROM treepaths WHERE tpa_ance = 4 and tpa_leng <> 0)
DELETE FROM treepaths 
WHERE tpa_desc IN cte AND tpa_ance NOT IN cte;
SELECT * FROM treepaths;


tpa_ance
tpa_desc
tpa_leng


1
1
0

1
2
1

1
3
2

1
4
3

2
2
0

2
3
1

2
4
2

3
3
0

3
4
1

4
4
0

5
5
0

5
6
1

6
6
0