Get all of the rows involved between tables relationships to delete them
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&lt;>fiddle here