| Author |
Topic |
|
noamg
Posting Yak Master
215 Posts |
 Posted - 2003-08-31 : 02:18:19
|
| hi,I have a table with ID int and parentID int.I have a store procedure which get an input param: ID.how to delete the sub-tree ?thanksNoam Graizer |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-31 : 06:52:38
|
| create table #a (id int)insert #a select @idwhile @@rowcount > 0insert #a select child_id from tbl, #a where tbl.id = #a.id and child_id not in (select id from #a)delete tbl from #a where #a.id = tbl.id==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
noamg
Posting Yak Master
215 Posts |
Posted - 2003-08-31 : 08:14:01
|
| what is child_id ???Noam Graizer |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-31 : 17:10:30
|
| sorrycreate table #a (id int)insert #a select @idwhile @@rowcount > 0insert #a select id from tbl, #a where tbl.parent_id = #a.id and tbl.id not in (select id from #a)delete tbl from #a where #a.id = tbl.id==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-31 : 20:40:32
|
| or, i think this might work if the root of the tree has a parentID of null and everyone else must have a parent:-- delete the parent:delete from tbl where id = @id_to_delete-- now delete all orphans until no more left to delete:while @rowcount > 0delete from tbl where (parent_id is not null) and parent_id not in (select id from tbl)- Jeff |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2003-09-01 : 08:10:15
|
| One small glitch, what if a foreign key constraint is enabled? jsmith8858's method won't work if RI is defined. I'm not sure if NR's method will work :O The delete should be outside of the while loop, which means that it will delete all entries at once, but is SQL Server sophisticated enough to pick up on all of an entries children being deleted and not throw a constraint violation error?<edit>Sorry for doubting you Sql Server! My quick test showed that it works as long as you really are deleting the whole sub-tree all at one time.As a side note, you can not specify ON DELETE CASCADE because you could theoretically have a cycle. I don't know how Sql Server would handle that. Most likely not well since they've gone through the trouble of making sure you couldn't do it.----------------------"O Theos mou! Echo ten labrida en te mou kephale!""Where theres a will, theres a kludge." - Ken Henderson |
|
|
|
maxim
Yak Posting Veteran
51 Posts |
Posted - 2007-09-04 : 20:00:53
|
Hello:Sorry to return a topic with 5 uers!I use this code on sql express 2005 databse and it works!But i don't understand! :(How can:while @rowcount > 0delete from tbl where (parent_id is not null) and parent_id not in (select id from tbl) delete all child fields from the table?It is because (select id from tbl) ?What is the id of the table? It's last deleted record ID ? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-09-05 : 12:52:36
|
| The logic breaks down to something like:1. If the parent_id of a record is not null then we assume that it should have a parent. 2. If the parent_id of the record in question does not exist then it must be an orphaned record, so go aheas and delte it.Does that make sense? If not, let us know maybe an example with data would help clarify.-Ryan |
|
|
|
maxim
Yak Posting Veteran
51 Posts |
Posted - 2007-09-13 : 12:53:52
|
| Yes! I understand! Thanks Lamprey! This method delete all records that don't have parentID in the table! |
|
|
|
|
delete an Hierarchy