Nested subquery is not supported in the DELETE condition

[diego_poggioli]

According to the documentation the WHERE predicate in a DELETE statement should supports subqueries, including IN, NOT IN, EXISTS, NOT EXISTS, and scalar subqueries.

if I try to run a query like:

DELETE FROM dev.gold.table AS trg
WHERE EXISTS (
    select distinct year_month, version
    from source
    where trg.year_month = year_month
    and trg.version = version
)

 I get the error: 

[DELTA_NESTED_SUBQUERY_NOT_SUPPORTED] Nested subquery is not supported in the DELETE condition.

Same error if I use IN after grouping the year_month and version into a single column:

DELETE FROM dev.gold.table AS trg
   WHERE year_month_version IN (select * FROM v_distinct_year_month_version)

Any solution, other than a merge delete, to solve the problem?

I'm running a SQL notebook with serverless warehouse.

[daniel_sahal]

@diego_poggioli 
What is the "source" in your WHERE statement? Is it a table or a view/temp view? 

[diego_poggioli]

@daniel_sahal currently is a temp view

[daniel_sahal · Accepted solution]

@diego_poggioli 
This could be it. Try materializing the view first and see if it fixes the issue.

[diego_poggioli]

Yes, correct! using a table instead of a view fixed the error

Thanks

[Tejas2022]

@diego_poggioli 

Can you try selecting a 'year_month_version' column from the view instead of select *

DELETE FROM dev.gold.table AS trg
   WHERE year_month_version IN (select year_month_version FROM v_distinct_year_month_version)