| Need SQL for Deleting Tree Data [message #19597] |
Mon, 01 April 2002 00:44  |
Skumar
Messages: 17
Registered: April 2002
|
Junior Member |
|
|
Is there a single sql query to delete a tree ( that is the parent and all its children and their children if they exist) .
I tried the following query
delete from abc
where abcid in ( select abcid from abc
connect by parent_id = prior node_id
start with node_id = 123
order by node_id desc)
since order by doesnot work inside subquery if i remove it .i get a error parent cannot be deleted since there are child records.
I will be thankful if some one gives me a solution.
One idea i got was to first select the id's order by desc with select statement push it into table type variable and in a for loop delete it. But this will be very heavy.
|
|
|
|
| Re: Need SQL for Deleting Tree Data [message #19610 is a reply to message #19597] |
Mon, 01 April 2002 14:45  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
|
Just because you order the subquery, doen't mean that the records would be deleted in the correct order. If you have a foreign key enforcing the parent-child relationship you could have a mutating table condition as you get with triggers. Sometimes you can overcome the problem of not having an order by in the subquery (not a problem on 8i) by using a group by. I would try doing it with pl/sql becuase of the difficulty in ensuring that records are deleted in the right order by using a single SQL statement.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
