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 |