Delete records in a tree structure with SQL


To answer a quertion at ASP.NET forum( http://forums.asp.net/t/1513336.aspx ).
Here is my example with CTE:
create table Locations
(LocationID int, Title varchar(50), description varchar(50), ParentLocationID int)
insert into Locations values (1,’aaa1′,’about aaa1′, null)
insert into Locations values (2,’aaa2′,’about aaa2′, 23)
insert into Locations values (3,’aaa3′,’about aaa3′, 24)
insert into Locations values (4,’aaa4′,’about aaa4′, 23)
insert into Locations values (5,’aaa5′,’about aaa5′, 24)
insert into Locations values (12,’aaa12′,’about aaa12′, 24)
insert into Locations values (13,’aaa13′,’about aaa13′, 12)
insert into Locations values (14,’aaa14′,’about aaa41′, 12)
insert into Locations values (15,’aaa15′,’about aaa15′, 12)
insert into Locations values (112,’aaa112′,’about aaa112′, 1)
insert into Locations values (113,’aaa113′,’about aaa113′, 15)
insert into Locations values (114,’aaa114′,’about aaa114′, 113)
insert into Locations values (115,’aaa115′,’about aaa115′, 114)
insert into Locations values (22,’aaa22′,’about aaa22′, 112)
insert into Locations values (23,’aaa23′,’about aaa23′, 1)
insert into Locations values (24,’aaa24′,’about aaa24′, 1)
insert into Locations values (25,’aaa5′,’about aaa25′, 23)
 
declare @LocationID int
set @LocationID =12
 
;WITH DirectReports (LocationID, ParentLocationID, Title, description, Level)
AS
(
— Anchor member definition
    SELECT e.LocationID, e.ParentLocationID,  e.Title, e.description , 0 AS Level
    FROM Locations AS e
        WHERE  e.LocationID=@LocationID
    UNION ALL
— Recursive member definition
    SELECT e.LocationID, e.ParentLocationID,  e.Title, e.description, Level + 1
     FROM Locations AS e INNER JOIN DirectReports AS d
        ON e.ParentLocationID= d.LocationID
)
— Statement that executes the CTE
—-SELECT LocationID, Title, description, ParentLocationID, Level
—-FROM DirectReports
—-order by Level,LocationID
–DELETE
DELETE Locations
FROM Locations L INNER JOIN DirectReports D ON L.LocationID=D.LocationID
SELECT * FROM Locations
drop table Locations
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s