使用一次后为什么不能访问我的CTE？ [英] why can't I access my CTE after I used it once?

问题描述

我的存储过程如下：

WITH MYCTE(....)
AS 
(
...
)

UPDATE ... (using my CTE)



DELETE ( using my CTE)  <---  says the object, my CTE, doesn't exist

我只能

推荐答案

在您的示例代码中，CTE仅对UPDATE持久。如果需要使其使用更长的时间，请考虑使用它填充#tempTable或@tableVariable，然后填充其中的UPDATE和DELETE。

In your example code, the CTE only persists for the UPDATE. If you need it to last longer, consider populating a #tempTable or @tableVariable with it, and then UPDATE and DELETE from those.

您还可以扩展UPDATE以使用 OUTPUT 子句，如下所示，因此您可以捕获受影响的行。并在DELETE中使用它们，例如：

You may also augment your UPDATE to use an OUTPUT clause, like the following, so you can capture the affected rows. And use them in the DELETE, like here:

set nocount on
DECLARE @Table     table (PK int, col1 varchar(5))
DECLARE @SavedPks  table (PK int)

INSERT INTO @Table VALUES (1,'g')
INSERT INTO @Table VALUES (2,'g')
INSERT INTO @Table VALUES (3,'g')
INSERT INTO @Table VALUES (4,'g')
INSERT INTO @Table VALUES (5,'x')
INSERT INTO @Table VALUES (6,'x')
set nocount off

;WITH MYCTE
AS 
(
  SELECT PK, col1 FROM @Table
)
UPDATE MYCTE
    SET col1='xyz'
    OUTPUT INSERTED.PK
        INTO @SavedPks
    WHERE col1='g'

SELECT 'A',* FROM @Table

DELETE @Table
    WHERE PK IN (SELECT PK  from @SavedPks)

SELECT 'B',* FROM @Table

输出：

(4 row(s) affected)
     PK          col1
---- ----------- -----
A    1           xyz
A    2           xyz
A    3           xyz
A    4           xyz
A    5           x
A    6           x

(6 row(s) affected)

(4 row(s) affected)

     PK          col1
---- ----------- -----
B    5           x
B    6           x

(2 row(s) affected)

这篇关于使用一次后为什么不能访问我的CTE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！