Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Feb 2004
    Location
    Richmond,va
    Posts
    4

    Question Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Richmond,va
    Posts
    4
    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!?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    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 work
    Error: 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)
    Last edited by r937; 02-25-04 at 11:44.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    you'll have to do a recursive user define function...

    (working on this RUDF..)

  6. #6
    Join Date
    Feb 2004
    Location
    Rhode Island
    Posts
    69

    Re: Complex Delet Query .. Help me!

    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

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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

  8. #8
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Complex Delet Query .. Help me!

    smasanam

    I'm almost there with my RUDF...
    just a few minutes more

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Complex Delet Query .. Help me!

    GOT IT GOOD

    coming up !

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Complex Delet Query .. Help me!

    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

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Complex Delet Query .. Help me!

    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)

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    my solution seems simpler than Brett's...


    wooooooooo
    risky saying this

  14. #14
    Join Date
    Feb 2004
    Location
    Richmond,va
    Posts
    4

    Talking Re: Complex Delet Query .. Help me!

    Karolyn

    Thanks a ton.. Iam trying this right now...

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Karolyn
    my solution seems simpler than Brett's...


    wooooooooo
    risky saying this
    troublemaker, heh?

    Anyway isn't

    Code:
    while @NewList<>'' 
    begin
    set @NewList=''
    False right away?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •