Get all of the rows involved between tables relationships to delete them

Question

Continuing with the question Get all of the rows involved between tables relationships, now I have to delete all the rows for those codes. I explain:

I have a SQL Server 2016 with the following tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            VARCHAR(20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

Aggregation and AggregationChildren represent relations between rows in table Code.

An aggregation will have 1 or more aggregation children. And also, a code in aggregation children could have 0 or more aggregation children.

It is something like this:

Code1
|___________________
|                   |
Code2               Code3
|________           |________________
|        |          |        |       |
Code4    Code5      Code6    Code7   Code8

All of the above are Code.Serial values.

All of the Code.CodeId values for those serials are in Aggregation table for Code1, Code2 and Code3.

And, for those serials, Code2, Code3, Code4, Code5, Code6, Code7, and Code8 are in AggregationChildren table.

I need to delete the rows for those serials from Aggregation and AggregationChildren tables.

Using the answer from the previous question, I have tried to do this:

DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
UPDATE c
SET    CommissioningFlag = 130
FROM   dbo.Code c
JOIN   rc
ON     c.CodeId = rc.Id;
GO

Delete ag from dbo.Aggregation ag
inner join rc 
on ag.AggregationId = rc.Id;

But I don't know how to use the DELETE FROM with the rc statement. But I think I only need the Codes that are in Aggregation and AggregationChildren. Maybe I need to modify the With statement to get first the rows in Aggregation table because I have an ON DELETE CASCADE in AggregationChildren table.

How can I get all the codes to delete from Aggregation table?

Answer 1

According to your table schema where there is a rule ON DELETE CASCADE in FK_AggregationChildren_Aggregation foreign key, you can delete only affected records of 'Aggregation' table.

Use the same recursive query to fetch rows but adding AggregationID of childrens, then delete distinct AggregationId codes.

DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id, null as Code
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id, AggregationID as Code
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
DELETE FROM dbo.Aggregation
WHERE AggregationID IN (SELECT DISTINCT Code FROM rc);

This is the result:

SELECT * FROM dbo.Aggregation;
GO

| AggregationId |
| ------------: |
|             9 |

SELECT * FROM dbo.AggregationChildren;
GO

AggregationChildrenId | AggregationId | Position
--------------------: | ------------: | -------:
                   10 |             9 |        1
                   11 |             9 |        2

db<>fiddle here