帮助删除程序 [英] Help with Delete Procedure

问题描述

好吧，如果我在表中有此数据

表格:
ID --- ParentID
1 ---- null
2 ----- 1
3 ----- 2
4 ----- 2
5 ----- null

如果删除ID = 1，则要删除与此ID绑定的所有内容
在此示例中，我想删除除ID = 5
外的所有内容 我该怎么办?

在这种情况下，它不起作用

Well, If I have this datas in table

Table:
ID --- ParentID
1 ---- null
2 ----- 1
3 ----- 2
4 ----- 2
5 ----- null

If I delete ID=1, I want to delete everything that binded with this ID
in this example I want to delete everything except ID=5
How can I do it?

In this case, It is not working

IF (EXISTS(SELECT * FROM dbo.Table_1 WHERE @ID=ParentID))
     DELETE FROM dbo.Table_1 where ID=@ID OR ParentID=@ID
ELSE
     DELETE FROM dbo.Table_1 where ID=@ID

推荐答案

我相信您有些困惑.给定ID = 1，您将只能删除ID = 2的记录，因为它是唯一直接链接到相关父级的记录.

您可以将表重组为具有父表和子表，并使用ON DELETE CASCADE

否则，您将需要一些递归查询来查找所有相关的子代，孙代，曾孙代等.

I believe you are somewhat confused. Given an ID = 1 you will only be able to delete the record with ID = 2 since it is the only one with a direct link to the parent in question.

You could restructure your tables to have parent and child tables and use ON DELETE CASCADE

Otherwise you are going to need some recursive query to find all related child, grandchildren, great-grandchildren, etc.

嗨Sider89，
试试这个代码.有效.

Hi Sider89,
Try this code. it works.

With Family As
(
    Select s.ID as aid, null as parentid,s.ID as mainparentid,  0 as Depth
    From tree s Where ID = 1
    Union All
    Select s2.ID, s2.ParentId,mainparentid, Depth + 1
    From tree s2
    Join Family
    On Family.aID = s2.ParentId  )
delete t From Family f join tree t on f.aid=t.id

这篇关于帮助删除程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！