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 |
|
melodyrain
Starting Member
2 Posts |
 Posted - 2008-10-10 : 03:31:50
|
| Hi everybody,Currently I am bothered by a tough question, well, hope it will be easy for you guys.I have three tables with following structure.Level(LevelId, ParentLevelId, LevelName)Area(AreaId, LevelId, ParentAreaId, AreaName)Tariff(TariffId, AreaId, TariffName)One level can have more than one aera, one area can have more than one tariff.When I delete a level with LevelId = '2', I want to delete all the related values in the three tables which are:1. All the sub-levels with ParentLevelId = '2' and all the sub-levels of the levels that have been deleted, keep doing this until all the related levels are deleted.2. All the Areas relate to Level which have been deleted in the Level table.3. All the sub-Areas with ParentAreaId equals to the areas that have been deleted and all the sub-Areas of the sub-Areas which have been deleted above. Keep recursively doing this until all the related areas are deleted.4. All the tariff with AreaId equals to the AreaId of Areas that have been deleted.It is very hard to describe, let alone achieve it in the database. I hope I show my question clearly.If anyone can provide the code for this example will be excellent and highly appreciated!Thanks in advanced.Hank |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-10 : 03:39:51
|
Read about recursive common table expressions in Books Online.There is an example of how to "recursive" delete.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
melodyrain
Starting Member
2 Posts |
Posted - 2008-10-11 : 00:59:13
|
quote:
Originally posted by Peso
Read about recursive common table expressions in Books Online.There is an example of how to "recursive" delete.
E 12°55'05.63"N 56°04'39.26"
Thank you for your reply.But I think my case is not that simple as all the simple ones that we can seach on the google.Could you or anyone else give more details about this?Thanks! |
|
|
|
|
|
|
|
|
Recursively deletion using stored procedure