Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

hierarchy delete

Posted on 2013-11-29
1
Medium Priority
?
163 Views
Last Modified: 2013-12-13
Hello,
How can I delete hierarchy from the root down? ParentId refers to Id from another row.
In the example below I would want to delete first 3 rows by passing in Id=1

DECLARE @Table table(Id int, ParentId int, MyText varchar(50))

INSERT INTO @Table(Id, ParentId, MyText) values(1,0,'One')
INSERT INTO @Table(Id, ParentId, MyText) values(2,1,'1.1')
INSERT INTO @Table(Id, ParentId, MyText) values(3,2,'1.1.1')
INSERT INTO @Table(Id, ParentId, MyText) values(4,0,'Two')
0
Comment
Question by:johnson1
1 Comment
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 39685441
use a recursive cte

;with cte as (
  select a.*
    from @table
    where id=1
  union all
  select a.*
    from @table as a
   Inner join cte as b
      on a.id=b.parentid
  )
 Delete x
   from @table as X
   Inner join CTE
     on x.id=cte.id
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question