Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Recursive delete help

Author  Topic  kfitch Starting Member 5 Posts Posted - 2001-06-05 : 11:59:39 Hi, I'm trying to perfrom a recursive delete and I can't seem to get anything to work. I've tried triggers and stored procedures but nothing does it right. My database consists of 2 tables. The table1 contains data with each row having an int identity. The table2 relates records in table1 to each other in a parent/child relationship using their identities. So: table1 ID Name -- -------- 1  Bob 2  Mary 3  John table2 Parent Child ------ ----- 1        2 2        3 From this we see that Mary is a child of Bob, and John is a child of Mary. I want to be able to delete a row in table1, and then recursively delete all children of that item. I don't know how many levels the relationships go so this must be recursive. Any ideas? (Assume children can have only one parent and there are no cyclical associations of parents and children. The data I am storing represents a hierarchical tree of arbitrary depth.)