Nested Sets and Delete/Exchange?

Hi all,

I use a simple nested set to repesent a hierarchical menu in SQL. I've read Joe Celkos article about trees at

http://www.intelligententerprise.com/001020/celko.shtml

and my simple menu table (in MySql) contains the fields id(bigint), menuname(varchar), left(int), right(int). So it's easy to build up a structure like this:

A(1,8)
| \

B(2,3) C(4,7)

         |
         D(5,6)

Unfortunately the reorganization of the tree is more complex and I can not find answers to my two questions on the net:

1. How to renumber the values for left and right after the deletion of an element? When I delete element C(4,7) in the tree, D and A have to change to D(4,5) and A(1,6). How to formulate this in SQL?
2. How to exchange two elements and their subtrees? After swapping elements B and C (their menu names) the tree should be structured like this:

A(1,8)
| \

C(2,5) B(6,7)
|
D(3,4)

The problem are the subtrees (D) which can hold a different number of elements. I tried to save the subtrees in a temporary table, delete them from the tree and then reinsert them under their swapped parents, but the copy/delete/reinsert takes a lot of SQL code. Is there any easier way to swap elements?

Kind regards,
Oliver Received on Sun Sep 21 2003 - 12:09:37 CDT