Get all of the rows involved between tables relationships

Question

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 update Code.CommissioningFlag for all of those serials.

I have created a stored procedure to called recursively. I pass Code1 as argument, and it will do a recursive call for Code2 and Code3.

This method works fine with a few codes, but now, I have to update 3,000 codes and it blocks SQL Server.

I've thought I have navigate throw the 'tree' and get a temp table with all of the codes, and the do and update for all of them, but I don't know how to 'navigate the tree' in SQL.

How can I get all the codes to update all of them at once?

Answer 1

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<>fiddle here