sql - why can't I access my CTE after I used it once? -

My stored procedure looks like this:

  MYCTE (....) Delete (...) update ... (by using my CTE) in the form (using my CTE) & lt; --- The object says, my CTE, does not exist  

Can I use it only once?

In your example code, CTE remains persistent for updates only if you need it to last longer If necessary, consider with a #tempTable or @tableVariable populating, and then update and delete them from them.

You can also increase your UPDATE to use a clause, you can capture the affected rows and use them in DELETE here:

  Set the nocount on the set @ Tables (PK Int, Cole 1 varchar (5)) DECLARE @SavedPks Table (PK Int) @ IN TERT VALUES INSERT (1, 'G') @ Valid values ​​(2, 'G') Insert @ Values ​​values ​​(3, 'G') inside INSERT @ values ​​(4, 'G') INSERT @ values ​​value (5, 'x') INSERT @Table VALUES (6, 'x') set knockout Close; In the form of the form (selection PK, call 1 from @table) Update set the Mikitset call 1 = 'Xx' INPRETED.PK @SavedPks where call 1 = 'G' Select 'A', * Settle Delay @Table WHERE PK IN (From SELECTPK @SavedPks) SELECT 'B', * FROM @Table  

OUTPUT:

(4 line (affected)) PK cola 1 - - ----------- ----- 1xyz A2xyz A3xyz A4xyz A5xA6X (6th row (affected)) (4 lines (affected)) PK Call 1 ---- ----------- ----- B5 x 6 x (2 rows) affected)