代码之家 › 专栏 › 技术社区 › LLL

删除具有外部约束的行,而不使用“级联”

with-statement tsql sql

LLL · 技术社区 · 13 年前

我是SQL的新手,因为我通常可以在不深入研究的情况下进行简单的查询/迁移,但我有一个更复杂的查询/移植,它不涉及联接,我想知道当存在外部约束时如何删除而不在级联上使用。这是我的疑问。我在谷歌上找不到一个好的答案。

WITH aDeleteVariable AS (SELECT TOP 1 FooID as id FROM [BSystem].[Foo].[bar] order by      
CreateTimeUTC desc)
DELETE FROM [BSystem].[Foo].[fooBar] where FooID = id
DELETE FROM [BSystem].[Foo].[bar] where FooID = id

所以我需要删除[BSystem]。首先是[Foo].fooBar,然后是[BSystem]。[Foo]。[bar]这将在运行时执行,因此不能硬编码。“1”实际上是n,但是我已经制定了代码。

我明白了 列名“id”无效 尝试执行此查询时。我该怎么修?

1 回复 | 直到 13 年前

gmm 13 年前

你的 DELETE 语句不引用 aDeleteVariable 这个 id 列仅存在于的上下文中 a删除变量 .

你需要加入 a删除变量 进入您的 删除 声明。此外,您不能重用公共表表达式,因此必须复制它。

看看这个 SQL Fiddle (with simplified schema/table names) ,但下面是您想要的。

WITH aDeleteVariable AS (SELECT TOP 1 FooID as id
        FROM [BSystem].[Foo].[bar] order by CreateTimeUTC desc)
DELETE FB 
        FROM [BSystem].[Foo].[fooBar] AS FB
        INNER JOIN aDeleteVariable AS DV ON (FB.FooID = DV.id);
WITH aDeleteVariable AS (SELECT TOP 1 FooID as id
        FROM [BSystem].[Foo].[bar] order by CreateTimeUTC desc)
DELETE B
        FROM [BSystem].[Foo].[bar] AS B
        INNER JOIN aDeleteVariable AS DV ON (B.FooID = DV.id);

当然,加入 a删除变量 ,你不需要 身份证件 别名以下内容将起作用。

WITH aDeleteVariable AS (SELECT TOP 1 FooID
        FROM [BSystem].[Foo].[bar] order by CreateTimeUTC desc)
DELETE FB 
        FROM [BSystem].[Foo].[fooBar] AS FB
        INNER JOIN aDeleteVariable AS DV ON (FB.FooID = DV.FooID);
WITH aDeleteVariable AS (SELECT TOP 1 FooID
        FROM [BSystem].[Foo].[bar] order by CreateTimeUTC desc)
DELETE B
        FROM [BSystem].[Foo].[bar] AS B
        INNER JOIN aDeleteVariable AS DV ON (B.FooID = DV.FooID);

假设有两个单独的公共表表达式,则存在竞争条件,第二个表达式可能返回不同的 FooID (或当您将其调整为 N 记录)。您可能希望将ID选择到表变量中,而不是使用公共表表达式。以下内容应该有效。

DECLARE @DeleteVariable TABLE (FooID INT);
INSERT INTO @DeleteVariable (FooID)
        SELECT TOP 1 FooID FROM [BSystem].[Foo].[bar]
                ORDER BY CreateTimeUTC DESC;
DELETE FB 
        FROM [BSystem].[Foo].[fooBar] AS FB
        INNER JOIN @DeleteVariable AS DV ON (FB.FooID = DV.FooID);
DELETE B
        FROM [BSystem].[Foo].[bar] AS B
        INNER JOIN @DeleteVariable AS DV ON (B.FooID = DV.FooID);