Cleaning up my database

[letimati]

Hi I am taking over a simple website database that has pages. Each page has a parent_id.

I have just looked at the database table and it has thousands of entrys. I looked into it a little and basically when a page was being deleted its children were not getting deleted.

I would like to clean the table up by deleteing eveything that does not have a active parent_id. I can do this for one level but the tree gos to 4 or five levels and everything under the top level all have active parent_id's. Is there a way or doing recursive sql?

thanks in advance for any help. I can quite easily write a quick script to do this but i love elegant sql to do stuff like this.

 

[PHV]

when a page was being deleted its children were not getting deleted.
So, you don't have any referential integrity ???

 

[r937]

can't recall them off the top of my head, but some databases do not allow cascading deletes to the same table (which is the case for hierarchies implemented with parent_id)

r937.com | rudy.ca