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
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With