16  

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...

检查DELETE语句文档，是的，您可以使用CTE删除它，它将影响基础表。同样对于UPDATE语句......

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

如果我有一个临时表而不是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.

不，从临时表中删除只会影响临时表 - 没有与数据来自的表的连接，临时表是一个独立的对象。

3  

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.

您可以将CTE视为子查询，它下面没有临时表。因此，如果对CTE运行delete语句，则会从表中删除行。当然，如果SQL可以根据您的CTE推断出哪个表是upadte / delete。否则你会看到一个错误。

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.

如果使用临时表，并从中删除行，则源表不会受到影响，因为临时表和原始表没有任何关联。

0  

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.

如果你有一个子查询说连接多个表，你需要在多个地方使用它，那么可以使用cte和temp表。但是，如果您想根据子查询条件删除记录，则可以使用cte。有时您可以简单地使用delete语句而不需要cte，因为它是一个delete语句，只有满足查询条件的行才会被删除，即使使用多个条件进行过滤也是如此。