How to Delete Parent Child Hierarchy Relation In SQL Server Using CTE

Main Menu

Programming

.NET Controls

Operating System

Databases

Articles

How to Delete Parent Child Hierarchy Relation In SQL Server Using CTE

You can delete the parent child hierarcy relation in SQL Server using the script below. It uses CTE first to delete the require records then it delete those record with the delete statement.

CREATE PROCEDURE [dbo].[prEntityLibraryDelete]
    (@LibraryID varchar(200))
AS
BEGIN
 
--DECLARE @LibraryID int
--set @LibraryID = 7;
 
WITH [CTE](LibraryID, [Name], Description, LibraryLevel, LibraryParentID, LibraryTypeID)
AS (
        SELECT LibraryID, [Name], Description, LibraryLevel, LibraryParentID, LibraryTypeID
        FROM [tblEntityLibrary] c
        WHERE (c.LibraryID = @LibraryID)
 
        UNION ALL
 
        SELECT c.*
            FROM [tblEntityLibrary] c INNER JOIN [CTE] p ON c.LibraryParentID = p.LibraryID        
)
 
DELETE FROM [tblEntityLibrary]
WHERE LibraryID IN(
    SELECT TOP 100 PERCENT t.LibraryID
    FROM [CTE] t
    ORDER BY t.LibraryLevel DESC)
 
OPTION ( MAXRECURSION 0 )
END

Comments (0)

Write comment

Your Contact Details:

Name:

Email:

Website:

Comment:

Title:

UBBCode:

Message:

Security

Please input the anti-spam code that you can read in the image.

< Prev		Next >

Resources

"