Solved

deleteing all hierarchical records under selected one

Posted on 2011-03-01
8
584 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

15 Experts available now in Live!

Get 1:1 Help Now