SQL Server 2008 Delete Records from Self-Referencing Table in correct order
- by KTrace
I need to delete a sub set of records from a self-referencing table in SQL Server 2008. I am trying to do the following but it is does not like the order by.
WITH SelfReferencingTable (ID, depth)
AS
(
SELECT id, 0 as [depth]
FROM dbo.Table
WHERE parentItemID IS NULL AND [t].ColumnA = '123'
UNION ALL
SELECT [t].ID, [srt].[depth] + 1
FROM dbo.Table t
INNER JOIN SelfReferencingTable srt ON [t].parentItemID = [srt].id
WHERE [t].ColumnA = '123'
)
DELETE y FROM dbo.Table y
JOIN SelfReferencingTable x on x.ID = y.id
ORDER BY x.depth DESC
Any ideas why this isn't working?
