Continuing with the question Get all of the rows involved between tables relationships, now I have to delete all the rows for those codes. I explain:
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 delete the rows for those serials from Aggregation
and AggregationChildren
tables.
Using the answer from the previous question, I have tried to do this:
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 = 130
FROM dbo.Code c
JOIN rc
ON c.CodeId = rc.Id;
GO
Delete ag from dbo.Aggregation ag
inner join rc
on ag.AggregationId = rc.Id;
But I don't know how to use the DELETE FROM
with the rc
statement. But I think I only need the Codes
that are in Aggregation
and AggregationChildren
. Maybe I need to modify the With
statement to get first the rows in Aggregation
table because I have an ON DELETE CASCADE
in AggregationChildren
table.
How can I get all the codes to delete from Aggregation
table?
Best Answer
According to your table schema where there is a rule
ON DELETE CASCADE
inFK_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.
This is the result:
db<>fiddle here