Cade.Bu,

It's like kwiggans said, a CTE is like a View:

Since I'm different from most (or is that contrary? :hehe: ), I like to think of CTEs as derived tables that I can alias then refer to as many times in my query as I want simply by using the alias. Actually, an argument can be made that a View is simply a derived table that you can access with out defining it for each query, you only define it the one time. But I digress, back to the issue.

So, a CTE works pretty much like a local view, and follows the same rules as a view in order for it to be updateable. Which means, an updateable CTE can be used just like an updateable view, you can perform Select, Insert or Delete queries on them.

Kwiggans,

The With statement is just the mechanism used to define the CTE. It's the fact that the CTE acts like a View that allows you to delete records through it.

I hope I helped more than I befuddled,

Kenney

Alternate Method:

This example can be done without the use of a CTE as well. All you have to do is to replace the CTE code and the delete statement with this piece of code that uses a derived table in a Delete statement to achieve the same goal:

DELETE NumberedDups

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 ORDER BY Col1, Col2, Col3 ) AS RowNum

FROM #DuplicateVals) AS NumberedDups

WHERE RowNum > 1

Performance Note:

An article I've read stated and showed that a CTE is faster than using a temp table, and I've found this to be true up to a point. The temp table is slower mostly due to the hard drive access need to create it in the first place, but this is just a onetime hit. The problem with the CTE is that SQL runs it each time you reference it in your query, so if your CTE takes 3 seconds to run and you use it 5 times in your query then you're looking at up to 15 seconds (or less due to caching) just for the CTE to be ran all 5 times and then you still have to add the time it takes for the rest of your query to run. But if you used a temp table then after the first hit of creating the temp table you start gaining time because it takes a lot less time to retrieve the records from a temp table than it does to reproduce the result set each time VIA a CTE (or any other query mechanism).

What does this mean? It means that if your CTEs are slow you will want to think twice about using them if you need to reference them multiple times in the same query batch.

Extra Study Idea:

You might want to look into a cool feature of CTEs. They can refer to themselves from within their own With statement (this is a recursive CTE). Books on Line has a little piece on this functionality where they show you how to get a list of someone(s) supervisor and their supervisor and their supervisor, etc. I think it's well worth a look since it opens up a path to do complicated things in a less complicated way.