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.
| Author |
Topic |
|
Ebad123
Starting Member
1 Post |
 Posted - 2012-02-21 : 01:55:05
|
| Hi,I have a table ParentTable in which I have parentId as primary key.Now I have three child tables namely ChildTable1, ChildTable2 and ChildTable3.In ChildTable1 = primary key is ch1Id and foreign key is fkparentId (pk from ParentTable )In ChildTable2 = primary key is ch2Id and foreign keys are fkparentId and fkch1Id (primary key from ChildTable1)In ChildTable3 = primary key is ch3Id and foreign keys are fkparentId, fkch1Id and fkch2Id (primary key from ChildTable2).I want to write a query to delete these tables hierarchially in the following manner.first delete ChildTable3 > then delete ChildTable2 > then delete ChildTable1 and finally delete ParentTable.I have a vague idea of doing this likedelete from ChildTable3 where fkch2Id = ch2.ch2Idand fkch1Id = ch1.ch1Idand fkparentId = p.parentIdthen proceeding to ChildTalbe2 and so on. Hope I made it clear. Help from you end will be appreciated thanks.Regards,Ebad Masood |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-21 : 07:04:10
|
| What you have there seems just fine. You would do similar thing for ChildTable2, ChildTable1 and then ParentTable. You have to do them in that order (Child3, Child2, Child1 and then Parent).You may want to wrap the deletes in a transaction and a try catch block to avoid the possibility that data from some tables may be deleted and others may be left undeleted if some type of error occurs during the deletions.Another option would be setting up cascade deletes - but personally I don't like that, so I will say nothing more about that. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-21 : 14:26:24
|
quote:
Originally posted by Ebad123
Hi,I have a table ParentTable in which I have parentId as primary key.Now I have three child tables namely ChildTable1, ChildTable2 and ChildTable3.In ChildTable1 = primary key is ch1Id and foreign key is fkparentId (pk from ParentTable )In ChildTable2 = primary key is ch2Id and foreign keys are fkparentId and fkch1Id (primary key from ChildTable1)In ChildTable3 = primary key is ch3Id and foreign keys are fkparentId, fkch1Id and fkch2Id (primary key from ChildTable2).I want to write a query to delete these tables hierarchially in the following manner.first delete ChildTable3 > then delete ChildTable2 > then delete ChildTable1 and finally delete ParentTable.I have a vague idea of doing this likedelete from ChildTable3 where fkch2Id = ch2.ch2Idand fkch1Id = ch1.ch1Idand fkparentId = p.parentIdthen proceeding to ChildTalbe2 and so on. Hope I made it clear. Help from you end will be appreciated thanks.Regards,Ebad Masood
seehttp://visakhm.blogspot.com/2011/11/recursive-delete-from-parent-child.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Hierarchial table deletion in SQL server 2008