markej
asked on
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?
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?
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 ;-) )
Example of use
exec DeleteNodeAndChilds 80
Full example
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
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
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)
(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
Hi,
Loop this from Bigger Id and delete one by one using cursor or logic like same
Loop this from Bigger Id and delete one by one using cursor or logic like same
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 )
"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 )
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats fixed it thanks
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.