How to delete parent and child record

Question

Hi I need some help.

I have a table that have LibraryID and LibraryParentID.

LibraryID int Unchecked
Name varchar(50) Checked
Description varchar(200) Checked
LibraryLevel int Checked
LibraryParentID int Checked
LibraryTypeID int Unchecked 

I want to write a sql to delete the currrent select parent and well as all child record, how can I do that?

Answer 1

Cascade the Delete on the foreign Key Here is a reference http://msdn.microsoft.com/en-us/library/aa933119(SQL.80).aspx

---

EDITED:

The comment is right below.

"The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE." quoted from MSDN

But here is some tsql that will help

Create Procedure RecursiveDelete(
 LibraryId Int )
AS
Declare @CursorLibraryID Int;

Declare myCursor Cursor FOR
SELECT LibraryParentID from Table;

Open myCursor;

Fetch Next
From MyCursor
Into @CursorLibraryID

While (@@Fetch_Status = 0) BEGIN
  Exec RecursiveDelete(@CursorLibraryID)
  Delete From Table
  Where LibraryID = @CursorLibraryId
Fetch Next
From MyCursor
Into CursorLibraryID

END

Well Its late and im tired so if the syntax is wrong I appologize. But the Idea here is you have have children, parents, grandparents, greatgrandparents...etc so you must recursively delete them.

Answer 2

Assuming you do not have cascade delete on the relationship enabled, you need to do this in two statements:

Delete Table
Where LibraryParentId = @Param

Delete Table
Where Id = @Param

Answer 3

To delete a the library and all parent libraries, assuming that the foreign key is a self-reference, you can do

WITH LibAncestors(libraryID, parentID, level) AS
(
  SELECT libraryID, libraryID, 0
  FROM   Library
  UNION ALL
   SELECT     a.libraryID, b.libraryParentID, a.level+1
   FROM   LibAncestors AS a INNER JOIN Library b
     ON b.libraryID = a.parentID
)
DELETE FROM Library 
 WHERE libraryID IN 
    (SELECT LibraryParentID FROM LibAncestors WHERE LibraryID=?)

Here ? is the placeholder for your LibraryID to delete, and it's parent, and it's parent and so on.

The CTE returns a libraryID in the first column, and all ancestors (including the library itself) in the second column.

The 3rd column is how far away the parent is from the child. 0 is for the child itself, 1 for it's parent, 2 for it's grandparent and so on. If you don't want to delete all parents, you can specify a restriction in the WHERE clause. For example, to delete the child and just it's immediate parent (as specified in the question)

DELETE FROM Library 
 WHERE libraryID IN 
    (SELECT LibraryParentID FROM LibAncestors WHERE LibraryID=? AND level<=1)

I don't know if this will work with foreign keys defined from libraryID to libraryParentID. But you can disable key checking and re-enable it again by using

ALTER TABLE Library NOCHECK CONSTRAINT ALL
DELETE FROM Library ....
ALTER TABLE Library CHECK CONSTRAINT ALL