topic Re: Nested subquery is not supported in the DELETE condition in Warehousing & Analytics

Nested subquery is not supported in the DELETE condition

diego_poggioli — Mon, 27 May 2024 12:00:28 GMT

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.

Re: Nested subquery is not supported in the DELETE condition

daniel_sahal — Mon, 27 May 2024 13:54:19 GMT

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

Re: Nested subquery is not supported in the DELETE condition

diego_poggioli — Mon, 27 May 2024 15:03:43 GMT

@daniel_sahal currently is a temp view

Re: Nested subquery is not supported in the DELETE condition

Tejas2022 — Mon, 27 May 2024 17:47:51 GMT

@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)

Re: Nested subquery is not supported in the DELETE condition

daniel_sahal — Tue, 28 May 2024 05:48:57 GMT

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

Re: Nested subquery is not supported in the DELETE condition

diego_poggioli — Tue, 28 May 2024 07:04:43 GMT

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

Thanks