Solved

deleteing all hierarchical records under selected one

Posted on 2011-03-01
8
625 Views
Last Modified: 2012-05-11
I have a Hierarchical datastructure and need to be able to delelete branches and everything under that branch and I can't figure out how to do it.

I know how to work up the tree from a node via parent but not this way.

The data I have is as follows:

id, parentid, menulevel
57, NULL, 1
74, 57, 2
75, 57, 2
76, 57, 2
77, 74, 3
78, 77, 4
79, 75, 2
80, 75, 2
81, 80, 3
82, 80, 3
83, 81, 4
84, 81, 4

I want to for example delete id 80 and all it's children i.e id's 81, 82, 83 and 84

How can I do this using SQL Server?
0
Comment
Question by:markej
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 4

Expert Comment

by:MarioAlcaide
ID: 35006605
You should have the DELETE CASCADE option on, for example:

ALTER TABLE catalog DROP CONSTRAINT aa

ALTER TABLE catalog ADD CONSTRAINT
   (FOREIGN KEY (stock_num, manu_code) REFERENCES stock
   ON DELETE CASCADE CONSTRAINT ab)

And then you just have to delete the parent records, and all the child records will be deleted.
0
 
LVL 10

Expert Comment

by:John Claes
ID: 35006652
You can do the folowing :

You can create the Procedure in Attach :
It deletes the Node you want and then deletes all the Nodes that do noet have a Existing ParentID

Procedure : (change #tmp towards your tablename ;-) )
 
create procedure DeleteNodeAndChilds
@NodeId int
as 
begin
	delete from #tmp where id =@NodeId
	while ((select count(*) from #tmp where parentid not in (select id from #tmp))>0)
	begin
		delete from #tmp where parentid not in (select id from #tmp)
	end
end

Open in new window


Example of use
exec DeleteNodeAndChilds 80

Full example
 
create procedure DeleteNodeAndChilds
@NodeId int
as 
begin
	delete from #tmp where id =@NodeId
	while ((select count(*) from #tmp where parentid not in (select id from #tmp))>0)
	begin
		delete from #tmp where parentid not in (select id from #tmp)
	end
end 
Go

create table #TMP
(
id int,
parentid int,
menulevel int
)

insert #tmp select 57, NULL, 1
insert #tmp select 74, 57, 2
insert #tmp select 75, 57, 2
insert #tmp select 76, 57, 2
insert #tmp select 77, 74, 3
insert #tmp select 78, 77, 4
insert #tmp select 79, 75, 2
insert #tmp select 80, 75, 2
insert #tmp select 81, 80, 3
insert #tmp select 82, 80, 3
insert #tmp select 83, 81, 4
insert #tmp select 84, 81, 4

select * from #tmp
exec DeleteNodeAndChilds 80
select * from #tmp
drop procedure DeleteNodeAndChilds
drop table #tmp

Open in new window

0
 
LVL 25

Expert Comment

by:reb73
ID: 35006657
If you are on SQL Server version 2005 or above, you could use a recursive cte -

(Note: Delete statement is commented out so that you can initially verify the selected results and then delete if ok)
declare @idchaintodelete int

set @idchaintodelete = 80  -- Populate the required hierarchy id here

;with cte (id, parentid, menulevel)
as
(	select id, parentid, menulevel
	from mytable
	where id = @idchaintodelete

	union all

	select m.id, m.parentid, m.menulevel
	from mytable m
	inner join cte on cte.id = m.parentid
)
select *
--delete
from cte

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35006726
Hi,

Loop this from Bigger Id and delete one by one using cursor or logic like same
0
 
LVL 10

Accepted Solution

by:
John Claes earned 250 total points
ID: 35006861
the delete of reb73 should be
delete from #tmp where id in (select id from cte )

a direct delete upon cte is not possible;-)

Derived table 'cte' is not updatable because the definition contains a UNION operator.

regards
poor beggar
0
 

Author Comment

by:markej
ID: 35006975
Thanks for all the suggestions. I've been trying reb73 suggestion with the modification added by poor_beggar and it all works except I get an error on the #tmp
"Invalid Object name '#tmp'"

Here's my Stored Procedure:
@id as integer

AS

with cte (id, parentid, menulevel)
as
(      select id, parentid, menulevel
      from radmenu
      where id = @id

      union all

      select m.id, m.parentid, m.menulevel
      from radmenu m
      inner join cte on cte.id = m.parentid
)


delete from #tmp where id in (select id from cte )
0
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 250 total points
ID: 35007031
change #tmp to radmenu
0
 

Author Comment

by:markej
ID: 35007270
Thats fixed it thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now