All we need is an easy explanation of the problem, so here it is.
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 )
AggregationChildren represent relations between rows in table
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
All of the
Code.CodeId values for those serials are in
Aggregation table for
And, for those serials,
Code8 are in
I need to delete the rows for those serials from
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
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
How can I get all the codes to delete from
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
According to your table schema where there is a rule
ON DELETE CASCADE in
FK_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.
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);
This is the result:
SELECT * FROM dbo.Aggregation; GO| AggregationId | | ------------: | | 9 |
SELECT * FROM dbo.AggregationChildren; GOAggregationChildrenId | AggregationId | Position --------------------: | ------------: | -------: 10 | 9 | 1 11 | 9 | 2
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂