Hi fellas,
Today I'm going to discuss how to do a cascade delete on a record set not stored in two tables (parent table and child table) but in a single table.
Let's imaging we have two tables Set_Folder and Set_Article. The database concept is that the folders in the system will be stored under Set_Folder table and the articles in those folders stored under Set_Article table. Don't be fussy, I'm applying cascade delete for one single table, not parent and child table. When it comes to between parent and child table, it's pretty much simple and straight forward but when it comes to a single table the code will be bit different and complex. Not much complex but not straight forward.
Set_Folder table :-
FolderId FolderName ParentId IsDeleted IsParentDeleted
1 folder 1 0 0 0
2 folder 2 1 0 0
3 folder 3 1 0 0
4 folder 4 2 0 0
5 folder 5 4 0 0
6 folder 6 0 0 0
7 folder 7 6 0 0
8 folder 8 0 0 0
According to the table structure root folders are the folders which has parentid as 0. All the other folders are sub folders. Therefor folder 1, folder 6 and folder 8 are root folders and all the others are sub folders.
The requirement is to delete an specific root folder with it's sub folders. Let's say user needs to delete folder 1. folder 2, folder 3, folder 4 and folder 5 should be deleted along with it since those are sub folders in folder 1.
To do this we have to use WITH key word along with OUTPUT key word.
This is how we gonna do it.
WITH q AS
(
SELECT Folderid
FROM Set_Folder
WHERE Folderid = 1
UNION ALL
SELECT tc.Folderid
FROM q
JOIN Set_Folder tc
ON tc.parentID = q.Folderid
)
UPDATE Set_Folder
SET IsDeleted = '1'
OUTPUT DELETED.*
WHERE EXISTS
(
SELECT Folderid
INTERSECT
SELECT Folderid
FROM q
)
In here we are doing a self join. I think you all are familiar with DELETED and INSERTED table. Those are like virtual tables and we will discuss about them in a separate section.
After running this query the table data set will be like,
FolderId FolderName ParentId IsDeleted IsParentDeleted
1 folder 1 0 1 1
2 folder 2 1 1 1
3 folder 3 1 1 1
4 folder 4 2 1 1
5 folder 5 4 1 1
6 folder 6 0 0 0
7 folder 7 6 0 0
8 folder 8 0 0 0
Let's say these folders have articles in them. The Set_Article table is like this.
ArticleId FolderId ArticleName IsDeleted
1 1 A1 0
2 1 A2 0
3 2 A3 0
4 3 A4 0
5 4 A5 0
6 5 A6 0
7 6 A7 0
8 7 A8 0
Requirement is to delete the root folder 1 and all it's sub folders and all the articles on those tables. Now we know how to delete the folders. Next step is to get the folder ids we deleted and delete all the articles in those folders. We can achieve this with below query.
WITH q AS
(
SELECT Folderid
FROM Set_Folder
WHERE Folderid = @FolderId
UNION ALL
SELECT tc.Folderid
FROM q
JOIN Set_Folder tc
ON tc.parentID = q.Folderid
)
UPDATE Set_Folder
SET IsParentDeleted = 1, IsDeleted = 1
OUTPUT DELETED.Folderid,
inserted.Folderid
INTO @table
WHERE EXISTS
(
SELECT Folderid
INTERSECT
SELECT Folderid
FROM q
)
AND IsDeleted = 0
What we are doing here is deleting the folders and insert those folder ids into a temporary table. Then we can simply get those deleted folder ids from the temporary table and delete relevant articles.
Today I'm going to discuss how to do a cascade delete on a record set not stored in two tables (parent table and child table) but in a single table.
Let's imaging we have two tables Set_Folder and Set_Article. The database concept is that the folders in the system will be stored under Set_Folder table and the articles in those folders stored under Set_Article table. Don't be fussy, I'm applying cascade delete for one single table, not parent and child table. When it comes to between parent and child table, it's pretty much simple and straight forward but when it comes to a single table the code will be bit different and complex. Not much complex but not straight forward.
Set_Folder table :-
FolderId FolderName ParentId IsDeleted IsParentDeleted
1 folder 1 0 0 0
2 folder 2 1 0 0
3 folder 3 1 0 0
4 folder 4 2 0 0
5 folder 5 4 0 0
6 folder 6 0 0 0
7 folder 7 6 0 0
8 folder 8 0 0 0
According to the table structure root folders are the folders which has parentid as 0. All the other folders are sub folders. Therefor folder 1, folder 6 and folder 8 are root folders and all the others are sub folders.
The requirement is to delete an specific root folder with it's sub folders. Let's say user needs to delete folder 1. folder 2, folder 3, folder 4 and folder 5 should be deleted along with it since those are sub folders in folder 1.
To do this we have to use WITH key word along with OUTPUT key word.
This is how we gonna do it.
WITH q AS
(
SELECT Folderid
FROM Set_Folder
WHERE Folderid = 1
UNION ALL
SELECT tc.Folderid
FROM q
JOIN Set_Folder tc
ON tc.parentID = q.Folderid
)
UPDATE Set_Folder
SET IsDeleted = '1'
OUTPUT DELETED.*
WHERE EXISTS
(
SELECT Folderid
INTERSECT
SELECT Folderid
FROM q
)
In here we are doing a self join. I think you all are familiar with DELETED and INSERTED table. Those are like virtual tables and we will discuss about them in a separate section.
After running this query the table data set will be like,
FolderId FolderName ParentId IsDeleted IsParentDeleted
1 folder 1 0 1 1
2 folder 2 1 1 1
3 folder 3 1 1 1
4 folder 4 2 1 1
5 folder 5 4 1 1
6 folder 6 0 0 0
7 folder 7 6 0 0
8 folder 8 0 0 0
Let's say these folders have articles in them. The Set_Article table is like this.
ArticleId FolderId ArticleName IsDeleted
1 1 A1 0
2 1 A2 0
3 2 A3 0
4 3 A4 0
5 4 A5 0
6 5 A6 0
7 6 A7 0
8 7 A8 0
Requirement is to delete the root folder 1 and all it's sub folders and all the articles on those tables. Now we know how to delete the folders. Next step is to get the folder ids we deleted and delete all the articles in those folders. We can achieve this with below query.
WITH q AS
(
SELECT Folderid
FROM Set_Folder
WHERE Folderid = @FolderId
UNION ALL
SELECT tc.Folderid
FROM q
JOIN Set_Folder tc
ON tc.parentID = q.Folderid
)
UPDATE Set_Folder
SET IsParentDeleted = 1, IsDeleted = 1
OUTPUT DELETED.Folderid,
inserted.Folderid
INTO @table
WHERE EXISTS
(
SELECT Folderid
INTERSECT
SELECT Folderid
FROM q
)
AND IsDeleted = 0
What we are doing here is deleting the folders and insert those folder ids into a temporary table. Then we can simply get those deleted folder ids from the temporary table and delete relevant articles.
