Delete rows from CTE in SQL SERVER

I have a CTE which is 开发者_运维问答a select statement on a table. Now if I delete 1 row from the CTE, will it delete that row from my base table?

Also is it the same case if I have a temp table instead of CTE?

Checking the DELETE statement documentation, yes, you can use a CTE to delete from and it will affect the underlying table. Similarly for UPDATE statements...

Also is it the same case if I have a temp table instead of CTE?

No, deletion from a temp table will affect the temp table only -- there's no connection to the table(s) the data came from, a temp table is a stand alone object.

You can think of CTE as a subquery, it doesn't have a temp table underneath.
So, if you run delete statement against your CTE you will delete rows from the table. Of course if SQL can infer which table to upadte/delete base on your CTE. Otherwise you'll see an error.

If you use temp table, and you delete rows from it, then the source table will not be affected, as temp table and original table don't have any correlation.

In the cases where you have a sub query say joining multiple tables and you need to use this in multiple places then both cte and temp table can be used. If you however want to delete records based on the sub query condition then cte is the way to go. Sometimes you can simply use the delete statement with out a need of cte since it's a delete statement and only rows that satisfy the query conditions get deleted even though multiple conditions are used for filtering.