|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Tree tableI have a "tree" table: Id - primary key ParrentId - (foreign key) related to Id Title ..... when I delete some record I want to delete it with all childs (cascade deleting). I can't set cascade deleting on the same table :(. Is there any easy way in the MSSQL 2005 to do this ? There is one idea - using cursors + recursive functions but I think this solution is not easy and elegant. Thakns for any help and sugestions. Regards. Andy Hi
You don't say what method you are using for your hierachy, but if it is a simple parent/child you can recurse using an exist clause e.g. CREATE TABLE #hierarchy ( parent_id int not null,Child_id int not null ) INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 0,1) -- root node INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,2) INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,3) INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,4) INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,5) INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 4,6) INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 3,7) INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 7,8) SELECT * FROM #hierarchy BEGIN TRANSACTION DELETE FROM #hierarchy WHERE child_id = 2 WHILE @@ROWCOUNT > 0 DELETE n FROM #hierarchy n WHERE NOT EXISTS ( SELECT * FROM #hierarchy h where h.child_id = n.parent_id ) AND n.parent_id <> 0 -- Exclude root SELECT * FROM #hierarchy ROLLBACK TRANSACTION John Show quote "Andrzej Jaworek" wrote: > Hello, > > I have a "tree" table: > > Id - primary key > ParrentId - (foreign key) related to Id > Title > ..... > > when I delete some record I want to delete it with all childs (cascade > deleting). I can't set cascade deleting on the same table :(. Is there > any easy way in the MSSQL 2005 to do this ? There is one idea - using > cursors + recursive functions but I think this solution is not easy > and elegant. > > Thakns for any help and sugestions. > > Regards. > > Andy > Hi, John
But the OP said that he has a PK-FK defined on the table between childid and parentid Show quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:A501C947-2AB6-421A-8923-0C5F2D3B6A6C@microsoft.com... > Hi > > You don't say what method you are using for your hierachy, but if it is a > simple parent/child you can recurse using an exist clause e.g. > > CREATE TABLE #hierarchy ( parent_id int not null,Child_id int not null ) > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 0,1) -- root node > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,2) > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,3) > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,4) > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,5) > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 4,6) > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 3,7) > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 7,8) > > SELECT * FROM #hierarchy > BEGIN TRANSACTION > DELETE FROM #hierarchy WHERE child_id = 2 > WHILE @@ROWCOUNT > 0 > DELETE n > FROM #hierarchy n > WHERE NOT EXISTS ( SELECT * FROM #hierarchy h where h.child_id = > n.parent_id ) > AND n.parent_id <> 0 -- Exclude root > > SELECT * FROM #hierarchy > > ROLLBACK TRANSACTION > > John > > "Andrzej Jaworek" wrote: > >> Hello, >> >> I have a "tree" table: >> >> Id - primary key >> ParrentId - (foreign key) related to Id >> Title >> ..... >> >> when I delete some record I want to delete it with all childs (cascade >> deleting). I can't set cascade deleting on the same table :(. Is there >> any easy way in the MSSQL 2005 to do this ? There is one idea - using >> cursors + recursive functions but I think this solution is not easy >> and elegant. >> >> Thakns for any help and sugestions. >> >> Regards. >> >> Andy >> OK..... but you can't use cascading deletes because it is self referencing..
but you can still loop CREATE TABLE nodes ( node_id int not null Constraint PK_Nodes PRIMARY KEY CLUSTERED,Parent_id int null ) ALTER TABLE nodes ADD CONSTRAINT FK_Parents FOREIGN KEY ( Parent_id ) REFERENCES nodes ( node_id ) INSERT INTO nodes ( node_id, parent_id ) values ( 1,NULL) -- root node INSERT INTO nodes ( node_id, parent_id ) values ( 2,1) INSERT INTO nodes ( node_id, parent_id ) values ( 3,1) INSERT INTO nodes ( node_id, parent_id ) values ( 4,2) INSERT INTO nodes ( node_id, parent_id ) values ( 5,2) INSERT INTO nodes ( node_id, parent_id ) values ( 6,4) INSERT INTO nodes ( node_id, parent_id ) values ( 7,3) INSERT INTO nodes ( node_id, parent_id ) values ( 8,7) SELECT * from nodes BEGIN TRANSACTION DELETE FROM nodes WHERE node_id = 2 WHILE @@ROWCOUNT > 0 DELETE n FROM nodes n WHERE NOT EXISTS ( SELECT * FROM nodes h where h.node_id = n.parent_id ) AND n.parent_id IS NOT NULL SELECT * from nodes ROLLBACK TRANSACTION I guess this could be put in an instead of trigger CREATE TRIGGER trg_deletenodes ON nodes INSTEAD OF DELETE AS IF @@ROWCOUNT = 0 RETURN SET NOCOUNT ON DELETE n FROM nodes n JOIN DELETED d on d.node_id = n.node_id WHILE @@ROWCOUNT > 0 DELETE n FROM nodes n WHERE NOT EXISTS ( SELECT * FROM nodes h where h.node_id = n.parent_id ) AND n.parent_id IS NOT NULL John Show quote "Uri Dimant" wrote: > Hi, John > But the OP said that he has a PK-FK defined on the table between childid > and parentid > > > > > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > news:A501C947-2AB6-421A-8923-0C5F2D3B6A6C@microsoft.com... > > Hi > > > > You don't say what method you are using for your hierachy, but if it is a > > simple parent/child you can recurse using an exist clause e.g. > > > > CREATE TABLE #hierarchy ( parent_id int not null,Child_id int not null ) > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 0,1) -- root node > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,2) > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,3) > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,4) > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,5) > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 4,6) > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 3,7) > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 7,8) > > > > SELECT * FROM #hierarchy > > BEGIN TRANSACTION > > DELETE FROM #hierarchy WHERE child_id = 2 > > WHILE @@ROWCOUNT > 0 > > DELETE n > > FROM #hierarchy n > > WHERE NOT EXISTS ( SELECT * FROM #hierarchy h where h.child_id = > > n.parent_id ) > > AND n.parent_id <> 0 -- Exclude root > > > > SELECT * FROM #hierarchy > > > > ROLLBACK TRANSACTION > > > > John > > > > "Andrzej Jaworek" wrote: > > > >> Hello, > >> > >> I have a "tree" table: > >> > >> Id - primary key > >> ParrentId - (foreign key) related to Id > >> Title > >> ..... > >> > >> when I delete some record I want to delete it with all childs (cascade > >> deleting). I can't set cascade deleting on the same table :(. Is there > >> any easy way in the MSSQL 2005 to do this ? There is one idea - using > >> cursors + recursive functions but I think this solution is not easy > >> and elegant. > >> > >> Thakns for any help and sugestions. > >> > >> Regards. > >> > >> Andy > >> > > > John Bell napisa³(a):
Show quote > OK..... but you can't use cascading deletes because it is self referencing.. Using above code I still receive reference error during deleting.> but you can still loop > > CREATE TABLE nodes ( node_id int not null Constraint PK_Nodes PRIMARY KEY > CLUSTERED,Parent_id int null ) > > ALTER TABLE nodes ADD CONSTRAINT FK_Parents FOREIGN KEY > ( Parent_id ) REFERENCES nodes ( node_id ) > > INSERT INTO nodes ( node_id, parent_id ) values ( 1,NULL) -- root node > INSERT INTO nodes ( node_id, parent_id ) values ( 2,1) > INSERT INTO nodes ( node_id, parent_id ) values ( 3,1) > INSERT INTO nodes ( node_id, parent_id ) values ( 4,2) > INSERT INTO nodes ( node_id, parent_id ) values ( 5,2) > INSERT INTO nodes ( node_id, parent_id ) values ( 6,4) > INSERT INTO nodes ( node_id, parent_id ) values ( 7,3) > INSERT INTO nodes ( node_id, parent_id ) values ( 8,7) > > SELECT * from nodes > BEGIN TRANSACTION > DELETE FROM nodes WHERE node_id = 2 > WHILE @@ROWCOUNT > 0 > DELETE n > FROM nodes n > WHERE NOT EXISTS ( SELECT * FROM nodes h where h.node_id = n.parent_id ) > AND n.parent_id IS NOT NULL > > SELECT * from nodes > > ROLLBACK TRANSACTION > > I guess this could be put in an instead of trigger > > CREATE TRIGGER trg_deletenodes ON nodes > INSTEAD OF DELETE > AS > IF @@ROWCOUNT = 0 RETURN > SET NOCOUNT ON > > DELETE n > FROM nodes n > JOIN DELETED d on d.node_id = n.node_id > > WHILE @@ROWCOUNT > 0 > DELETE n > FROM nodes n > WHERE NOT EXISTS ( SELECT * FROM nodes h where h.node_id = n.parent_id ) > AND n.parent_id IS NOT NULL > > > John Andy Hi
Sorry I guess I must have deleted the FK somehow. If that is not an option, then you will need to traverse the tree and delete it bottom up. If you are using SQL 2005 this could be done with a CTE, if you are on an earlier version then you would probably need to use a temporary table or table variable and a cursor when deleting the nodes to abide by the FK. John Show quote "John Bell" wrote: > OK..... but you can't use cascading deletes because it is self referencing.. > but you can still loop > > CREATE TABLE nodes ( node_id int not null Constraint PK_Nodes PRIMARY KEY > CLUSTERED,Parent_id int null ) > > ALTER TABLE nodes ADD CONSTRAINT FK_Parents FOREIGN KEY > ( Parent_id ) REFERENCES nodes ( node_id ) > > INSERT INTO nodes ( node_id, parent_id ) values ( 1,NULL) -- root node > INSERT INTO nodes ( node_id, parent_id ) values ( 2,1) > INSERT INTO nodes ( node_id, parent_id ) values ( 3,1) > INSERT INTO nodes ( node_id, parent_id ) values ( 4,2) > INSERT INTO nodes ( node_id, parent_id ) values ( 5,2) > INSERT INTO nodes ( node_id, parent_id ) values ( 6,4) > INSERT INTO nodes ( node_id, parent_id ) values ( 7,3) > INSERT INTO nodes ( node_id, parent_id ) values ( 8,7) > > SELECT * from nodes > BEGIN TRANSACTION > DELETE FROM nodes WHERE node_id = 2 > WHILE @@ROWCOUNT > 0 > DELETE n > FROM nodes n > WHERE NOT EXISTS ( SELECT * FROM nodes h where h.node_id = n.parent_id ) > AND n.parent_id IS NOT NULL > > SELECT * from nodes > > ROLLBACK TRANSACTION > > I guess this could be put in an instead of trigger > > CREATE TRIGGER trg_deletenodes ON nodes > INSTEAD OF DELETE > AS > IF @@ROWCOUNT = 0 RETURN > SET NOCOUNT ON > > DELETE n > FROM nodes n > JOIN DELETED d on d.node_id = n.node_id > > WHILE @@ROWCOUNT > 0 > DELETE n > FROM nodes n > WHERE NOT EXISTS ( SELECT * FROM nodes h where h.node_id = n.parent_id ) > AND n.parent_id IS NOT NULL > > > John > "Uri Dimant" wrote: > > > Hi, John > > But the OP said that he has a PK-FK defined on the table between childid > > and parentid > > > > > > > > > > > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > > news:A501C947-2AB6-421A-8923-0C5F2D3B6A6C@microsoft.com... > > > Hi > > > > > > You don't say what method you are using for your hierachy, but if it is a > > > simple parent/child you can recurse using an exist clause e.g. > > > > > > CREATE TABLE #hierarchy ( parent_id int not null,Child_id int not null ) > > > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 0,1) -- root node > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,2) > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,3) > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,4) > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,5) > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 4,6) > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 3,7) > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 7,8) > > > > > > SELECT * FROM #hierarchy > > > BEGIN TRANSACTION > > > DELETE FROM #hierarchy WHERE child_id = 2 > > > WHILE @@ROWCOUNT > 0 > > > DELETE n > > > FROM #hierarchy n > > > WHERE NOT EXISTS ( SELECT * FROM #hierarchy h where h.child_id = > > > n.parent_id ) > > > AND n.parent_id <> 0 -- Exclude root > > > > > > SELECT * FROM #hierarchy > > > > > > ROLLBACK TRANSACTION > > > > > > John > > > > > > "Andrzej Jaworek" wrote: > > > > > >> Hello, > > >> > > >> I have a "tree" table: > > >> > > >> Id - primary key > > >> ParrentId - (foreign key) related to Id > > >> Title > > >> ..... > > >> > > >> when I delete some record I want to delete it with all childs (cascade > > >> deleting). I can't set cascade deleting on the same table :(. Is there > > >> any easy way in the MSSQL 2005 to do this ? There is one idea - using > > >> cursors + recursive functions but I think this solution is not easy > > >> and elegant. > > >> > > >> Thakns for any help and sugestions. > > >> > > >> Regards. > > >> > > >> Andy > > >> > > > > > > With a cursor you would have to delete the lowermost child nodes first, then
ascend the tree deleting the parents of the deleted children until you get to your top node that you need to delete. You could also use CTEs if you are on SQL Server 2005. Another alternative would be to change your table structure to give you more flexibility. This would be a major change, but it would make your life easier in the long run. You can get a lot of ideas from TREES AND HIERARCHIES by Joe Celko, or you can search the web for other solutions. One that I find useful is to store the begin node (usually refers to the current node) and the end node (refers to the last descendent of the current node). This requires that your tree be ordered in a specific way, but allows you to delete from the tree WHERE NODE BETWEEN BEGINNODE AND ENDNODE. Good luck. Show quote "John Bell" <jbellnewspo***@hotmail.com> wrote in message news:C25A47A1-9775-4E99-92F9-7F3739F2ACE0@microsoft.com... > Hi > > Sorry I guess I must have deleted the FK somehow. If that is not an option, > then you will need to traverse the tree and delete it bottom up. If you are > using SQL 2005 this could be done with a CTE, if you are on an earlier > version then you would probably need to use a temporary table or table > variable and a cursor when deleting the nodes to abide by the FK. > > John > > "John Bell" wrote: > > > OK..... but you can't use cascading deletes because it is self referencing.. > > but you can still loop > > > > CREATE TABLE nodes ( node_id int not null Constraint PK_Nodes PRIMARY KEY > > CLUSTERED,Parent_id int null ) > > > > ALTER TABLE nodes ADD CONSTRAINT FK_Parents FOREIGN KEY > > ( Parent_id ) REFERENCES nodes ( node_id ) > > > > INSERT INTO nodes ( node_id, parent_id ) values ( 1,NULL) -- root node > > INSERT INTO nodes ( node_id, parent_id ) values ( 2,1) > > INSERT INTO nodes ( node_id, parent_id ) values ( 3,1) > > INSERT INTO nodes ( node_id, parent_id ) values ( 4,2) > > INSERT INTO nodes ( node_id, parent_id ) values ( 5,2) > > INSERT INTO nodes ( node_id, parent_id ) values ( 6,4) > > INSERT INTO nodes ( node_id, parent_id ) values ( 7,3) > > INSERT INTO nodes ( node_id, parent_id ) values ( 8,7) > > > > SELECT * from nodes > > BEGIN TRANSACTION > > DELETE FROM nodes WHERE node_id = 2 > > WHILE @@ROWCOUNT > 0 > > DELETE n > > FROM nodes n > > WHERE NOT EXISTS ( SELECT * FROM nodes h where h.node_id = n.parent_id ) > > AND n.parent_id IS NOT NULL > > > > SELECT * from nodes > > > > ROLLBACK TRANSACTION > > > > I guess this could be put in an instead of trigger > > > > CREATE TRIGGER trg_deletenodes ON nodes > > INSTEAD OF DELETE > > AS > > IF @@ROWCOUNT = 0 RETURN > > SET NOCOUNT ON > > > > DELETE n > > FROM nodes n > > JOIN DELETED d on d.node_id = n.node_id > > > > WHILE @@ROWCOUNT > 0 > > DELETE n > > FROM nodes n > > WHERE NOT EXISTS ( SELECT * FROM nodes h where h.node_id = n.parent_id ) > > AND n.parent_id IS NOT NULL > > > > > > John > > "Uri Dimant" wrote: > > > > > Hi, John > > > But the OP said that he has a PK-FK defined on the table between childid > > > and parentid > > > > > > > > > > > > > > > > > > "John Bell" <jbellnewspo***@hotmail.com> wrote in message > > > news:A501C947-2AB6-421A-8923-0C5F2D3B6A6C@microsoft.com... > > > > Hi > > > > > > > > You don't say what method you are using for your hierachy, but if it is a > > > > simple parent/child you can recurse using an exist clause e.g. > > > > > > > > CREATE TABLE #hierarchy ( parent_id int not null,Child_id int not null ) > > > > > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 0,1) -- root node > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,2) > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 1,3) > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,4) > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 2,5) > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 4,6) > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 3,7) > > > > INSERT INTO #hierarchy ( parent_id, Child_id ) values ( 7,8) > > > > > > > > SELECT * FROM #hierarchy > > > > BEGIN TRANSACTION > > > > DELETE FROM #hierarchy WHERE child_id = 2 > > > > WHILE @@ROWCOUNT > 0 > > > > DELETE n > > > > FROM #hierarchy n > > > > WHERE NOT EXISTS ( SELECT * FROM #hierarchy h where h.child_id = > > > > n.parent_id ) > > > > AND n.parent_id <> 0 -- Exclude root > > > > > > > > SELECT * FROM #hierarchy > > > > > > > > ROLLBACK TRANSACTION > > > > > > > > John > > > > > > > > "Andrzej Jaworek" wrote: > > > > > > > >> Hello, > > > >> > > > >> I have a "tree" table: > > > >> > > > >> Id - primary key > > > >> ParrentId - (foreign key) related to Id > > > >> Title > > > >> ..... > > > >> > > > >> when I delete some record I want to delete it with all childs (cascade > > > >> deleting). I can't set cascade deleting on the same table :(. Is there > > > >> any easy way in the MSSQL 2005 to do this ? There is one idea - using > > > >> cursors + recursive functions but I think this solution is not easy > > > >> and elegant. > > > >> > > > >> Thakns for any help and sugestions. > > > >> > > > >> Regards. > > > >> > > > >> Andy > > > >> > > > > > > > > > Where's --CELKO-- when you most expect him?
Perhaps he's too busy blogging? Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood Show quote "Andrzej Jaworek" <SPAMandrzejjaworekSPAM@o2.pl> wrote in message news:ee5muo$nsk$2@atlantis.news.tpi.pl... > Hello, > > I have a "tree" table: > > Id - primary key > ParrentId - (foreign key) related to Id > Title > .... > > when I delete some record I want to delete it with all childs (cascade > deleting). I can't set cascade deleting on the same table :(. Is there any > easy way in the MSSQL 2005 to do this ? There is one idea - using cursors > + recursive functions but I think this solution is not easy and elegant. > > Thakns for any help and sugestions. > > Regards. > > Andy  |
|||||||||||||||||||||||
Other interesting topics