Delete Reccords from a table recursively - SQL Server 2008

Question

Table Structure

ID       DESC             PARENT_ID**

35151    Parent            35154
35152    System            35151
35153    Same as System    35151
35154    ParentsParent     35157
35156    Product           35157
35157    Login Group       35159

Where

Id is the primary key, and parent_id is the foreign key referenced in the same table

How can I delete records recursively, starting from the last child till the parent. If there is no child records the parent record should be deleted. I need to use transaction with rollback if something odd happens. Help appreciated

Answer 1

Look to this question:

http://stackoverflow.com/questions/1783700/sql-server-self-reference-fk-trigger-instead-of-on-delete-cascade

the FK with ON DELETE CASCADE does not works in SQL Express 2005, may be it will works in 2008

ALTER TABLE SomeChildTable 
CONSTRAINT YOurConstraintName 
FOREIGN KEY (YourParentId)
REFERENCES YourParentTable(ParentTableId) ON DELETE CASCADE;

Answer 2

You can use a recursive CTE to get the list to be deleted.

http://odata.stackexchange.com/stackoverflow/q/9297/so

-- SO3466713

CREATE TABLE #t (
    ID int NOT NULL
    ,[DESC] varchar(50) NOT NULL
    ,PARENT_ID int NULL
)

INSERT INTO #t VALUES
(35151, 'Parent', 35154)
,(35152, 'System', 35151)
,(35153, 'Same as System', 35151)
,(35154, 'ParentsParent', 35157)
,(35156, 'Product', 35157)
,(35157, 'Login Group', 35159)

;WITH tree AS (
    SELECT *
    FROM #t
    WHERE [DESC] = 'Parent'

    UNION ALL

    SELECT c.*
    FROM #t AS c
    INNER JOIN tree AS p
        ON c.PARENT_ID = p.ID
)
-- SELECT *
-- FROM tree
DELETE FROM #t WHERE ID IN (SELECT ID FROM tree)

SELECT * FROM #t​