是否有使用 CTE 使用 DELETE 雪花 SQL 语句进行查询的替代方法? [英] is there an alternative to query with DELETE snowflake SQL statement with CTE?

问题描述

在snowflake 上，是否有使用CTE 的DELETE SQL 语句进行查询的替代方法?好像不可能.

On snowflake, is there an alternative to query with DELETE SQL statement with CTE? seems it is not possible.

with t as (
select *  from "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."CALL_CENTER"
), p as (select t.CC_REC_END_DATE, t.CC_CALL_CENTER_ID , t.CC_REC_START_DATE from t where 1=1 AND t.CC_REC_START_DATE > '2000-01-01')

delete from p

例如:如果我们使用选择，我们会得到一些结果.

For example: if we use a select, we got some results.

但是如果我使用删除.它显示一个语法错误

but if I use a delete. It shows a syntax error

推荐答案

这种想法的问题在于 SELECT 返回一些值，这些值可能与表中的单个记录匹配，也可能不匹配.原则上，它们可能会返回来自多个表、多行或什至根本没有行的值的偶数组合.那么您希望 DELETE 做什么?

The problem with this thinking is that SELECT returns some values, that might, or might not be, matching individual records in your table. In principle, they might return even combinations of values from multiple tables, multiple rows, or no rows at all even. What would you want DELETE to do then?

因此，对于 DELETE，您需要指定要删除表中的哪些行.您可以使用简单的 WHERE 子句或 USING 子句来实现.

So, for DELETE, you need to specify which rows in the table you're operating on are deleted. You can do it with a simple WHERE clause, or with a USING clause.

如果你想要带有 DELETE 的 CTE，最接近的是使用 USING，在那里放置一个子查询，并将其结果与表连接.在许多情况下，这是一种非常有用的方法，但它会导致连接，从而影响性能.示例:

If you want CTEs with DELETE, the closest would be to use USING, put a subquery there, and join its result with the table. In many cases that's a very useful approach, but it is causing a join, which has a performance impact. Example:

delete from CALL_CENTER t
using (
  select cc_call_center_sk from CALL_CENTER
  where 1=1 AND t.CC_REC_START_DATE > '2000-01-01'
) AS sub
where sub.cc_call_center_sk = t.cc_call_center_sk.

但同样，对于您的查询，它没有多大意义，@cddt 写的内容可能是您最好的选择.

But again, for your query it doesn't make much sense, and what @cddt wrote is probably your best bet.

这篇关于是否有使用 CTE 使用 DELETE 雪花 SQL 语句进行查询的替代方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！