Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    1

    Unanswered: updating nodes in a nested set model

    We have a nested set L and R design in our database.
    The design allows multiple instances of nodes in the hierarchy. Each node has a combination of node name and its instance id as the primary key. We also maintain a unique_qty column that has the unique number of nodes below a particular node. This unique qty basically ignores the multiple instances of nodes below it and counts only the distinct node names(ignoring their instance ids).
    the problem that im facing is...how do i update the unique_qty when i perform any move in the tree.
    UPDATE Hierarchy
    SET unique_qty = ( SELECT COUNT(DISTINCT node_name) FROM Hierarchy AS H2 WHERE H2.L > Hierarchy.L AND H2.R < Hierarchy.R )

    I am using the above query to find out the unique_qty when i initially populate the table.
    My question is.. when i make a move of a subtree within the hierarchy, then i need to update this unique_qty for the source parents and the destination parents of the subtree( and the unique_qty for eah node in the subtree being moved remains the same ).
    I had two ideas on how to update the parent nodes of the subtree:

    1) for each node in the path to the root in the hierarchy, from the parent nodes( both source side and destination side) recalculate the unique_qty

    2) for each node in the subtree find out until what level in the path to the root, we need to update the unique_qty, and then update only those unique_qtys

    any suggestions on my methods? which one is better? any more ideas on how to do this??

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    this is for respondents - more info here:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72039

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    precious little "more info" in that other thread, my boy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    precious little "more info" in that other thread, my boy
    It is still an active thread, old fella
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Watch it, whipper-snapper! And mind yourself, you old coot!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •