主页 / dba / 问题 / 205132
Accepted
VansFannel
Asked: 2018-04-28 00:09:38 +0800 CST

获取表关系之间涉及的所有行以删除它们

  • 772

继续问题Get all of the rows involved between tables relationships，现在我必须删除这些代码的所有行。我解释一下：

我有一个带有下表的 SQL Server 2016：

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并AggregationChildren表示表中行之间的关系Code。

聚合将有 1 个或多个子聚合。而且，聚合子项中的代码可以有 0 个或多个聚合子项。

它是这样的：

Code1
|___________________
|                   |
Code2               Code3
|________           |________________
|        |          |        |       |
Code4    Code5      Code6    Code7   Code8

以上都是Code.Serial价值观。

Code.CodeId这些连续出版物的所有值都在和的Aggregation表Code1中。Code2Code3

并且，对于那些连续剧，Code2、Code3、Code4、Code5、Code6、Code7和Code8在AggregationChildren表中。

Aggregation我需要从和AggregationChildren表中删除那些连续出版物的行。

使用上一个问题的答案，我尝试这样做：

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;

但我不知道如何使用DELETE FROMwithrc语句。但我想我只需要Codes在Aggregation和中的那些AggregationChildren。也许我需要修改With语句以首先获取表中的行，Aggregation因为我有一个ON DELETE CASCADEinAggregationChildren表。

如何从Aggregation表中删除所有代码？

sql-server t-sql

1 个回答

  1. Best Answer

    根据外键中有规则的表架构ON DELETE CASCADE，FK_AggregationChildren_Aggregation您只能删除“聚合”表的受影响记录。

    使用相同的递归查询来获取行，但添加子项的 AggregationID，然后删除不同的 AggregationId 代码。

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

    这是结果：

    SELECT * FROM dbo.Aggregation;
GO

    | 聚合 ID |
| ----------: |
| 9 |

    SELECT * FROM dbo.AggregationChildren;
GO

    聚合子 ID | 聚合 ID | 位置
------------------: | ----------: | ------:
                   10 | 9 | 1个
                   11 | 9 | 2个

    db<>fiddle here

    • 2

相关问题