Delete N Level Parent Child in Single Table

Question

Delete N Level Parent Child in Single Table

gaur1982

Valued Member

Points: 66
More actions
August 4, 2010 at 12:09 am

#140120

ALTER procedure DeleteNLevelData
@Id int
as
WITH CatTB(id)
AS
(
SELECT Id
FROM tblCategory
WHERE Id = @Id
UNION ALL
SELECT C.Id
FROM tblCategory AS C
JOIN CatTB AS CT
ON C.ParentId = CT.Id
)
delete from tblCategory where Id in
(
SELECT Id FROM CatTB
)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Is there a question here?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

WayneS SSC Guru Points: 95460 More actions · Answer 2

You can't use a CTE in SQL 7/2000 (which is the forum that you posted in).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2