SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            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)
);

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
);
INSERT INTO dbo.Code VALUES
(1,  'Code1',  1, 1),
(2,  'Code2',  2, 1),
(3,  'Code3',  2, 1),
(4,  'Code4',  3, 1),
(5,  'Code5',  3, 1),
(6,  'Code6',  3, 1),
(7,  'Code7',  3, 1),
(8,  'Code8',  3, 1),
(9,  'Code9',  1, 3),
(10, 'Code10', 2, 3),
(11, 'Code11', 2, 3);
11 rows affected

INSERT INTO dbo.Aggregation VALUES (1),(2),(3),(9);
4 rows affected

INSERT INTO dbo.AggregationChildren VALUES
(2,  1, 1),
(3,  1, 2),
(4,  2, 1),
(5,  2, 2),
(6,  3, 1),
(7,  3, 2),
(8,  3, 3),
(10, 9, 1),
(11, 9, 2);
9 rows affected

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);
3 rows affected

SELECT * FROM dbo.Aggregation;


AggregationId


9


SELECT * FROM dbo.AggregationChildren;


AggregationChildrenId
AggregationId
Position


10
9
1

11
9
2