删除SQL表中的分层数据 [英] Deleting hierarchical data in SQL table

问题描述

我有一个包含分层数据的表格.
列ParentId"，其中包含其父项的 Id(ID" - 键列).

I have a table with hierarchical data.
A column "ParentId" that holds the Id ("ID" - key column) of it's parent.

删除一行时，我想删除所有子项(所有级别的嵌套).

When deleting a row, I want to delete all children (all levels of nesting).

怎么做?

谢谢

推荐答案

当行数不是太大时，erikkallen 的递归方法有效.

When the number of rows is not too large, erikkallen's recursive approach works.

这是使用临时表收集所有子项的替代方法:

Here's an alternative that uses a temporary table to collect all children:

create table #nodes (id int primary key)
insert into #nodes (id) values (@delete_id)
while @@rowcount > 0
    insert into #nodes 
    select distinct child.id 
    from table child
    inner join #nodes parent on child.parentid = parent.id
    where child.id not in (select id from #nodes)

delete
from table
where id in (select id from #nodes)

它从带有@delete_id 的行开始并从那里下降.where 语句是为了防止递归；如果您确定没有，则可以将其省略.

It starts with the row with @delete_id and descends from there. The where statement is to protect from recursion; if you are sure there is none, you can leave it out.

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