Recurive query: Delete subtree - Help!!

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recurive query: Delete subtree - Help!!

Author

Topic

webressurs
Starting Member

5 Posts

Posted - 2007-11-01 : 14:08:52

Is there a way to do a "recursive join" (SQL Server 2005)? In other words, I'd like a table to link back to itself. For example, I have a single table that stores articles in multiple levels (treeview), how can I query all articles from a particular article? Let's assume that the article table has the following fields:

ID
ParentId
Name
Active

What I need to make is a Query that set "Active=0" for every articles under one spesific article. For example, I want to delete Article7 (set Active=0), then Article8-9-10 should be deleted too (Active=0):

Article1
Article2
|- Article 4
|   |- Article6
|   |- Article7
|       |-Article8
|       |-Article9
|       |-Article10
|- Article 5
Article3

www.WebRessurs.no

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-01 : 14:42:29

Look up CTE (Common Table Expressions) in Books online

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 14:52:39

On our Hierarchy tables we have a PATH column (basically a delimited list of all the Parent IDs), so we can select all rows that are Children of a given Node - and then, e.g., delete on the basis of that

Kristen

webressurs
Starting Member

5 Posts

Posted - 2007-11-01 : 15:33:37

Hi, I did my best but get the error "The statement terminated. The maximum recursion 100 has been exhausted before statement completion".

Here is my code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spDeleteSubTree]
(
@Id smallint
)
AS
BEGIN

SET NOCOUNT ON;

with TreeCTE(Id) as
(
Select id from tblContent where id = @Id
union all
select c.id from tblContent c
join TreeCTE d on c.Id = d.Id
)
select id from TreeCTE;

END

www.WebRessurs.no

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-01 : 15:52:18

Do you really have 100 levels in your tree?
I don't think so. You are using the wrong recursive part in the CTE.

Content.Id = Content.Id ? You are joining same column to itself. What else columns do you have? A parentID column perhaps?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources