SQLite Closure Tabe: How to improve my SQL to split a tree-CodePudding

here is a classic tree paths table:

    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)
);

Initial data:

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

where I split that tree using the following SQL:

DELETE FROM treepaths WHERE tpa_desc IN 
(SELECT tpa_desc FROM treepaths WHERE tpa_ance = 4 and tpa_leng <> 0)
AND tpa_ance NOT IN
(SELECT tpa_desc FROM treepaths WHERE tpa_ance = 4 and tpa_leng <> 0);

Result:

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

the job is done but I'm wondering if a better SQL can be written using SQLite avoiding to repeat the same code: (SELECT tpa_desc FROM treepaths....
Thanks in advance for your help

CodePudding user response：

One simplification that you can do is to use a CTE instead of the subquery:

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;

See the demo.