Delete rows from CTE in SQL SERVER

Question

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?

Solution 1

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.

Solution 2

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.

Comments

[satyajit]

So, Will it be correct to say that CTE works as a temporary view

[satyajit]

How does CTE differ from Temp table in this scenario?

[Alex Aza]

@satyajit - You can say that. It will not lead to any db object creation though. Temp table on the contrary will lead to creation a table in tempdb and physical copying data to the table from your source tables.

[OMG Ponies]

@satyajit: A temp table is a table (can have different scopes: local, global) that stores data in the TEMPDB (rather than the DB it was created in). A CTE is a derived table/inline view, that exists only for the scope of the query it is declared in -- using resources from the current DB.