Menu
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?
946
Now, you can now see that the execution plan only scans the Posts and Users tables once each, instead of once for every UNION ALL set. The CTE runs only once.
Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler.
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
Cannot use with CTE"SELECT DISTINCT", GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries are not allowed in the CTE query definition of a recursive member. A CTE can be self-referencing and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
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.
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) Yep, the WITH MYCTE clause is not creating a permanent object to use in multiple queries afterwards: it's only modifying the one query you're adding that clause to! If you need very different functonality, consider, instead, using views...
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
