sql server 2005 - Deleting hierarchical data in SQL table

Question

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

How to do it?

Thanks

Answer 1

On SQL Server: Use a recursive query. Given CREATE TABLE tmp(Id int, Parent int), use

WITH x(Id) AS (
    SELECT @Id
    UNION ALL
    SELECT tmp.Id
      FROM tmp
      JOIN x ON tmp.Parent = x.Id
)
DELETE tmp
  FROM x
  JOIN tmp ON tmp.Id = x.Id