batch processing - How to prune dead branches in a hierarchical data structure -

batch processing - How to prune dead branches in a hierarchical data structure -

we have table has int nodeid column primary key , self bring together int parentid column , varchar(25) name column.

we need query delete dead branches given set of leaf nodes (or bottom level nodes). example, if given set of nodes below, if nodeid 4 passed in, node 4 should deleted , since node 2 no longer has children, should deleted. node 1 still has kid (node 6) process stop there.

[0] root / \ [1] node 1 [3] node 3 / \ \ [2] node 2 [6] node 6 [5] node 5 / [4] node 4

i have solution using cursor, avoid cursors whenever possible , much prefer batch means of accomplishing this. there way cte? or other means?

the cte below returns parents. i've made couple effort alter lastly select delete node includes single kid or no children. nil i've tried along lines executes.

any approach have have means of walking hierarchy , deleting nodes without children.

any other ideas or approaches welcome.

with nancestry (parentid, nodeid, name, ancestryid) ( select n.parentid, n.nodeid, n.name, 0 ancestryid node n n.nodeid=@nodeid union select n.parentid, n.nodeid, n.name, ancestryid + 1 node n inner bring together nancestry on a.parentid = n.nodeid ) select parentid, nodeid, name nancestry nodeid <>0 order ancestryid desc

batch-processing common-table-expression hierarchical-data recursive-query