Get all of the rows involved between tables relationships
According to your explanation I think you could use a recursive solution. I've set up a minimal example:




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);
GO






INSERT INTO dbo.Aggregation VALUES (1),(2),(3),(9);
GO






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);
GO






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

SELECT CodeId FROM dbo.Code WHERE Serial = @Serial;

SELECT    AggregationId Id
FROM      dbo.Aggregation
WHERE     AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial);
GO



| CodeId |
| -----: |
|      1 |

| Id |
| -: |
|  1 |



The anchor selects first row from dbo.Aggregation, and the recursive query rows from dbo.AggregationChildren where AggregationId match AggregationId of dbo.Aggregation.

Then simply update Code table using the list of ID's returned by the recursive query.




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 = 5
FROM   dbo.Code c
JOIN   rc
ON     c.CodeId = rc.Id;
GO



8 rows affected






SELECT * FROM dbo.Code;
GO



CodeId | Serial | AggregationLevelId | CommissioningFlag
-----: | :----- | :----------------- | :----------------
     1 | Code1  | 1                  | 5                
     2 | Code2  | 2                  | 5                
     3 | Code3  | 2                  | 5                
     4 | Code4  | 3                  | 5                
     5 | Code5  | 3                  | 5                
     6 | Code6  | 3                  | 5                
     7 | Code7  | 3                  | 5                
     8 | Code8  | 3                  | 5                
     9 | Code9  | 1                  | 3                
    10 | Code10 | 2                  | 3                
    11 | Code11 | 2                  | 3                



db&lt;>fiddle here