Complex Delet Query .. Help me!

I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
---- ---- ----
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.have you tried ON DELETE CASCADE in the foreign key relationship?

i've never done this myself (cascading delete in the adjacency model hierarchy), so i'd be very interested in finding out that it works|||I have not had much SQL experience.. Im more of a C#/C++/asp.net developer! Moreover Iam deleting the records in the same table.. so there is no foriegn key or anything here.. did I miss something!?|||create table yourtable
( SECID integer
, PARENTID integer
, NAME varchar(50)
, primary key (SECID)
, constraint validparent
foreign key (PARENTID)
references yourtable (SECID)
on delete cascade
)

-- load table

delete from yourtable where SECID=8001

Edit: nope, that don't workError: Introducing FOREIGN KEY constraint 'validparent' on table 'yourtable' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. (State:37000, Native Code: 6F9)
Error: Could not create constraint. See previous errors. (State:37000, Native Code: 6D6)|||you'll have to do a recursive user define function...

(working on this RUDF..)|||Originally posted by netjkus
I have a complex delete query.
the table : secid, parentid are int.

SECID PARENTID NAME
---- ---- ----
8000 NULL NULL
8001 8000
8002 8000
8003 8002
8004 8002
8005 8003
8006 8003
8007 8001 NULL
8008 8001 NULL
8009 8007 NULL
8010 8007 NULL
8011 8009 NULL

as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.

This is a complex query. I never heard or came accross like this. The parent-->child is nesting many times. I am not sure you can do it in one SQL. Might have to use a store procedure and keep the nodes in a temp table. Delete from the main table until no records found in temp table|||Check this one - with triggers (they have to be recursive - database option)...

drop table test
create table test (id int primary key,pid int)
go
insert test values(1,1)
insert test values(2,1)
insert test values(3,1)
insert test values(4,2)
go
select * from test
go
create trigger tr_test on test
for delete
as
if not exists(select * from deleted)
return
delete test where pid in(select id from deleted)
go
delete test where id=2-- or 1|||smasanam

I'm almost there with my RUDF...
just a few minutes more|||GOT IT GOOD

coming up !|||Create these functions

CREATE FUNCTION Trim (@.Mot Varchar(230))
RETURNS Varchar(230) AS
BEGIN
return(Rtrim(Ltrim(@.Mot)))
END

CREATE Function ListOfChildren (@.Parents Varchar(100))
RETURNS Varchar(100)
As
Begin
Declare @.List Varchar(100),
@.NewList VarChar(100),
@.TotalList VarChar(100)

set @.List=@.Parents
set @.NewList='abc'
set @.TotalList='|' + @.Parents

while @.NewList<>''
begin
set @.NewList=''
Select @.NewList = @.NewList + '|' + dbo.Trim(SectID) From Family Where PatIndex('%' +dbo.Trim(ParentID)+ '%', @.List )<>0
set @.List= @.NewList
set @.TotalList=@.List+@.TotalList
end

Return(@.TotalList)
End|||then you can do :

delete family
where patindex('%' + dbo.Trim(sectid) +'%' , dbo.listofchildren('8007'))<>0 or
patindex('%' + dbo.Trim(parentid) +'%' , dbo.listofchildren('8007'))<>0

in fact the function ListOfChildren returns for 8007 the list of :
all children (8009,8010)
all children of children (8011)
the named parent (8007)|||http://www.sqlteam.com/item.asp?ItemID=8866

Or

http://archives.postgresql.org/pgsql-sql/2002-11/msg00358.php|||my solution seems simpler than Brett's...

wooooooooo
risky saying this|||Karolyn

Thanks a ton.. Iam trying this right now...|||Originally posted by Karolyn
my solution seems simpler than Brett's...

wooooooooo
risky saying this

troublemaker, heh?

Anyway isn't

while @.NewList<>''
begin
set @.NewList=''

False right away?|||nope 'cause it's set to 'abc'

need gllasssseesss ?|||(ding ding ding)
ROUND 1

Karolyn : right strong punch
Brett : KO|||and when you do the total

Brett : 1289
Karolyn : 1

catching up !!!|||How right you are...the evaluation doesn't come until the END...|||I can't answer lots of question
so when I do
I make a big fuss out of it
(a Frenchy-of-France-Attitude that I learned here in France)|||BUT STILL...
your

code:------------------------
while @.NewList<>''
begin
set @.NewList=''
------------------------
False right away?


was a VERY Low one|||Very nice...

USE Northwind
GO

CREATE TABLE family(SECtID varchar(10), PARENTID varchar(10))
GO

INSERT INTO family(SECtID, PARENTID)
SELECT '8000', NULL UNION ALL
SELECT '8001', '8000' UNION ALL
SELECT '8002', '8000' UNION ALL
SELECT '8003', '8002' UNION ALL
SELECT '8004', '8002' UNION ALL
SELECT '8005', '8003' UNION ALL
SELECT '8006', '8003' UNION ALL
SELECT '8007', '8001' UNION ALL
SELECT '8008', '8001' UNION ALL
SELECT '8009', '8007' UNION ALL
SELECT '8010', '8007' UNION ALL
SELECT '8011', '8009'
GO

SELECT dbo.ListOfChildren(8007)
GO

DROP FUNCTION TRIM
DROP FUNCTION ListOfChildren
DROP TABLE family
GO

And I like the fact that is comes out in asceding hierarchy, with the grandfather of it all last...|||Originally posted by Karolyn
BUT STILL...
your



was a VERY Low one

Well I apologize if I offended you.

Listen, that's why I'm a scrub...I missed it...

(had nothing to do with trying to put you down...a very silly game I don't play)|||Karolyn

Thanks a ton.. It works!! You made my day!! Iam implementing it !