Any way to recursively update a tree in SQL?

Question

I've got a database table that represents a bunch of trees. The first three columns are GUIDs that look like this:

NODE_ID (PK)
PARENT_NODE_ID (FK to same table, references NODE_ID)
TREE_ID (FK to another table)

It's possible to move a node to a different tree. The tricky part is bringing all its child-nodes with it. That takes a recursive update. (And yes, I realize this is kinda bad design in the first place. I didn't design it. I just have to maintain it, and I can't change the database schema.)

It would be nice if I could do the update in SQL, as a stored procedure. But I can't think of how to implement the recursive operation required in set logic, without employing a cursor. Does anyone know of a reasonably simple way to pull this off?

Answer 1

If you are using Postgres or MS SQL 2005 you can use a recursive update, otherwise, you may want to consider using a method other than an adjacency list. I saw a presentation a few weeks ago speaking about these issues and storing hierarchical data. Here is a link:

http://www.slideshare.net/billkarwin/practical-object-oriented-models-in-sql

Start @ slide 40