SQL: DELETE data from self-referencing table in specific order

Question

I have a table like this:

groupId guid PK
parentId guid
name
left int
right int

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

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).

---

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'`

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.

What I want is the equivalent of:

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

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.

I also tried:

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

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?

Answer 1

Try

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