Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

Share this Page URL

3.5 Deleting a Single Node > 3.5 Deleting a Single Node - Pg. 53

3.5 Deleting a Single Node 39 The second search condition is there to prevent a table scan and to restrict the results to the immediate subordinates. 3.3 Searching for Superiors Given a node, find all of its superiors. This requires disassembling the path back into the identifiers that constructed it. We can use a table of sequential integers to find the required substrings: SELECT SUBSTRING (P1.path_string FROM (seq * CHAR_LENGTH(P1.emp_id)) FOR CHAR_LENGTH(P1.emp_id)) AS emp_id FROM Personnel_OrgChart AS P1, Series AS S1 WHERE P1.emp_id = :search_emp_id AND S1.seq < = CHAR_LENGTH(path_string)/CHAR_LENGTH(emp_id); The problem is that this does not tell you the relationships among the superiors, only who they are. Those relationships are actually easier to report. SELECT P2.* FROM Personnel_OrgChart AS P1, Personnel_OrgChart AS P2 WHERE P1.emp_id = :search_emp_id AND POSITION (P2.path_string IN P1.path_string) = 1; 3.4 Deleting a Subtree Given a node, delete the subtree rooted at that node. This can be done with the same predicate as finding the subordinates: DELETE FROM Personnel_OrgChart WHERE path_string LIKE (SELECT path_string FROM Personnel_OrgChart WHERE emp_id = :dead_guy) | | `%'; 3.5 Deleting a Single Node Once more we have to face the problem that when a nonleaf node is removed from a tree, it is no longer a tree and we need to have rules for changing the structure.