CODEWITHSUNDEEP
sqlsql-deletesnowflake-cloud-data-platform
Erick Salas-Chaverri
Erick Salas-Chaverri

Reputation: 138

is there an alternative to query with DELETE snowflake SQL statement with CTE?

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. CTE with simple SQL select

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

CTE with delete

Upvotes: 3

Views: 12847

Answers (3)

alvaro nortes
alvaro nortes

Reputation: 630

Hi your example is not very clear but in general yes, you can use a delete statement with a CTE in Snowflake: For example:

DELETE FROM YOUR_TABLE USING (
    WITH CTE1 AS (SELECT FIELD1 FROM TABLE1)
        SELECT FIELD1 FROM CTE1
    ) AS CTE_RESULT
WHERE YOUR_TABLE.FIELD1 = CTE_RESULT.FIELD1

Upvotes: 1

Marcin Zukowski
Marcin Zukowski

Reputation: 4729

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?

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.

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.

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

Upvotes: 5

cddt
cddt

Reputation: 549

The expected outcome from the question is not clear, so this is my best interpretation of it.

It seems like you want to delete records from the table SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER where the field CC_REC_START_DATE is greater than 2000-01-01. If that's what you want to achieve, then you don't need a CTE at all. 

DELETE FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER t
WHERE t.CC_REC_START_DATE > '2000-01-01'

Upvotes: 0

Related Questions