• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • 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 ClaesCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Alpesh PatelAssistant ConsultantCommented:
Hi,

Loop this from Bigger Id and delete one by one using cursor or logic like same
0
 
John ClaesCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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