My stored procedure looks like:
WITH MYCTE(....)
AS
(
...
)
UPDATE ... (using my CTE)
DELETE ( using my CTE) <--- says the object, my CTE, doesn't exist
Can I only use it once?
See Question&Answers more detail:osMy stored procedure looks like:
WITH MYCTE(....)
AS
(
...
)
UPDATE ... (using my CTE)
DELETE ( using my CTE) <--- says the object, my CTE, doesn't exist
Can I only use it once?
See Question&Answers more detail:osIn 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.
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
OUTPUT:
(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)