To Remove Nodes
There are three types to remove node in the hierarchical data as follows.
- To remove all nodes entire sub-tree of base node.
- To remove all descendant nodes of base node.
- To remove the base node, and then move up the descendant nodes.
To remove all nodes entire sub-tree of base node.
To remove sub-tree nodes is so easy. Because can use WHERE clause of query to find sub-tree.
| Query to Find Subtree of [B] | SQL to Remove Subtree of [B] |
|---|---|
mysql> SELECT *
FROM ff_tables
WHERE ff_queue >= 1
AND ff_queue <=
COALESCE(
(SELECT MIN(ff_queue) - 1
FROM ff_tables
WHERE ff_queue > 1
AND ff_depth <= 1
),
0x7fffffff
)
; |
mysql> DELETE
FROM ff_tables
WHERE ff_queue >= 1
AND ff_queue <=
COALESCE(
(SELECT MIN(ff_queue) - 1
FROM ff_tables
WHERE ff_queue > 1
AND ff_depth <= 1
),
0x7fffffff
)
; |
To remove all descendant nodes of base node.
It is so easy to remove descendant nodes of base node as same as subtree. Follows is to remove descendant nodes of node [B].
mysql> DELETE
FROM ff_tables
WHERE ff_queue > 1
AND ff_queue <=
COALESCE(
(SELECT MIN(ff_queue) - 1
FROM ff_tables
WHERE ff_queue > 1 AND ff_depth <= 1
),
0x7fffffff
)
;
To remove the base node, and then move up the descendant nodes.
It's a little complicated that to remove base node and move up the descendant nodes of base node. Need to execute "DELETE statement for removing base node" and "UPDATE statement to move up DEPTH of descendant nodes of base node".
DELETE and UPDATE statements are as:
mysql> BEGIN;
mysql> DELETE
FROM ff_tables
WHERE ff_queue = 1
;
mysql> UPDATE ff_tables
SET ff_depth = ff_depth - 1
WHERE ff_queue > 1
AND ff_queue <=
COALESCE(
(SELECT MIN(ff_queue) - 1
FROM ff_tables
WHERE ff_queue > 1 AND ff_depth <= 1
),
0x7fffffff
)
;
mysql> COMMIT;