• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

deleteing all hierarchical records under selected one

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
markej
Asked:
markej
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
MarioAlcaideCommented:
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
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
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
 
reb73Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Alpesh PatelAssistant ConsultantCommented:
Hi,

Loop this from Bigger Id and delete one by one using cursor or logic like same
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
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
 
markejAuthor Commented:
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
 
reb73Commented:
change #tmp to radmenu
0
 
markejAuthor Commented:
Thats fixed it thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now