SQL:按特定顺序从自引用表中删除数据 [英] SQL: DELETE data from self-referencing table in specific order

问题描述

我有一张这样的桌子:

groupId guid PK
parentId guid
name
left int
right int

从 parentId 到 groupId 有一个外键(这是一个自引用表).

There is a foreign key from parentId to groupId (this is a self-referencing table).

left 和 right 是 MPTT 左/右值，用于维护层次结构.这里需要注意的重要一点是，左值越大，一个项目的嵌套越深(换句话说:对于任何给定的项目，它的左值总是大于所有父项的左值).

left and right are MPTT left/right values for maintaining a hierarchy. The important thing to note here is the bigger the left value, the more deeply-nested an item is (in other words: for any given item, its left value will always be bigger than the left value of all parents).

我正在尝试编写一个快速的 DELETE 语句来删除除最顶层组(始终具有空 GUID 值)之外的所有内容，例如:

I'm trying to write a quick DELETE statement to delete everything but the top-most group (which always has an empty GUID value), eg:

DELETE FROM [group] WHERE [groupId] <> '00000000-0000-0000-0000-000000000000'`

不幸的是，这不起作用，因为通常在任何给定组下面都有子组阻止删除.如果您一遍又一遍地运行该 DELETE 查询，最终它会删除所有内容，但这显然不是一个好的解决方案.

Unfortunately this doesn't work, as there are generally sub-groups below any given group that prevent deletion. If you run that DELETE query over and over, eventually it will delete everything, but this is obviously not a good solution.

我想要的相当于:

DELETE FROM [group] WHERE [groupId] <> '00000000-0000-0000-0000-000000000000' 
ORDER BY [left] DESC

当然，这是不允许的语法，但实际上，它应该首先删除具有最大左值的项目，以确保下面没有组会阻止由于 FK 约束而导致删除.

Of course, that isn't allowed syntax, but effectively, it should delete the items with the largest left values first to ensure there are no groups below that will prevent deletion due to FK constraints.

我也试过:

delete from [group] where groupid in (
  select top 1000000 * from [group] 
  where groupid <> '00000000-0000-0000-0000-000000000000' 
  ORDER BY [left] desc
)

这是有效的语法(如果您也使用 TOP，则只能使用 ORDER BY)但实际上并没有导致 DELETE 按返回行的顺序发生，因此它仍然不起作用.

which is valid syntax (you can only use ORDER BY if you also use TOP) but doesn't actually cause the DELETE to happen in the order of returned rows, so it still doesn't work.

这是否可以做到，而无需借助游标逐一删除行?

Is this possible to do, without resorting to a cursor to delete rows one-by-one?

推荐答案

尝试

ALTER TABLE [group] NOCHECK CONSTRAINT ALL
go
DELETE FROM [group] WHERE [groupId] <> '00000000-0000-0000-0000-000000000000'
go
ALTER TABLE [group] CHECK CONSTRAINT ALL
go

如果桌子很大，这可能是一个不错的选择.

If the table is large, this might be a good option.

select * into #temptable from [group] WHERE [groupId] = '00000000-0000-0000-0000-000000000000'
go
truncate table [group]
go
select * into [group] from #temptable
go
drop table #temptable
go

这篇关于SQL:按特定顺序从自引用表中删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！