Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deleting hierarchical data in SQL table

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

like image 964
markiz Avatar asked May 19 '09 12:05

markiz


People also ask

Which of the following deletes not using the hierarchical data type?

Which of the code deletes node H using hierarchical data type? Explanation: Deleting a node does not automatically delete the child nodes, this would result in orphaned children. 9.

How do you handle SQL hierarchy?

The easiest way to migrate from a Parent/Child structure to a table using hierarchyid is to use a temporary column or a temporary table to keep track of the number of nodes at each level of the hierarchy. For an example of migrating a Parent/Child table, see lesson 1 of Tutorial: Using the hierarchyid Data Type.


1 Answers

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
like image 95
erikkallen Avatar answered Oct 29 '22 17:10

erikkallen