Query for updating a node in hierarchial tree structure

Rated 3.92/5 based on 820 customer reviews

In this article we will examine two models for dealing with hierarchical data in My SQL, starting with the traditional adjacency list model.

But I don't think I've ever seen an article on my way of managing hierarchies in SQL, and that's using what I call a closure table.The technique is an answer to the problem that the standard relational algebra and relational calculus, and the SQL operations based on them, are unable to express all desirable operations on hierarchies directly.A hierarchy can be expressed in terms of a parent-child relation - Celko calls this the adjacency list model - but if it can have arbitrary depth, this does not allow the expression of operations such as comparing the contents of hierarchies of two elements, or determining whether an element is somewhere in the subhierarchy of another element.Likewise if you need to be able to find all the children of X at any moment, or be able check within another query whether any of X's ancestors are Y.It really doesn't work for that sort of thing at all.

Leave a Reply