Nested Set Model: Inserting Node at the end of SubNodes

Existing Data (name, lft, rgt):

Root, 1, 4
Item1, 2, 3

Looks like:

- Root
--- Item1

How do you insert a new node (Item2) BELOW Item1? My system's current logic follows most examples I've found online but the result is Item2 ABOVE Item1.

- Root
--- Item1
--- Item2

Thank you for the help.

Think of the nested sets model as of an XML file with lft and rgt being the lines where the staring and ending tags reside:

1  <root>
2   <item1>
3   </item1>
4  </root>

To insert a new subtag into the root, you'll need to shift down all subsequent records:

1  <root>
2   <item1>
3   </item1>
4   <item2>
5   </item2>
6  </root>

So you'll need to calculate the item2.lft and item2.rgt (which are the item2.rgt + 1 and item1.rgt + 2, accordingly), and then increment all lft and rgt of all items which are greater than the item1.rgt:

UPDATE  mytable
SET     rgt = rgt + 2
WHERE   rgt > item1.rgt

UPDATE  mytable
SET     lft = lft + 2
WHERE   lft > item1.rgt

I don't think the "correct answer" is correct, what you should do is: set item2.lft = root.rgt (4) set item2.rgt = root.rgt + 1 (5)

UPDATE mytable SET rgt = rgt + 2 WHERE rgt >= root.rgt root.rgt is 6 now;

UPDATE mytable SET lft = lft + 2 WHERE lft > root.rgt root.lft stays the same (1), but you have multiple roots, they will get updated.

You can't use a peer entry to determine the lft and rgt values.

Root, 1, 6
Item1, 2, 3
Item2, 4, 5

and use ORDER BY ItemName, but of course this only works for a given set of siblings.

For how to insert, see the 5th query here: http://intelligent-enterprise.informationweek.com/001020/celko.jhtml;jsessionid=OOU0L1TIM1IB1QE1GHPSKH4ATMY32JVN

Some people put gaps between their numbers to try to minimize the number of UPDATEs that have to be done. This performance issue can only be partially alleviated though, and is intrinsic to the nested set model. For this reason many people implement other hierarchical models, like materialized path.