cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Warehousing & Analytics
Engage in discussions on data warehousing, analytics, and BI solutions within the Databricks Community. Share insights, tips, and best practices for leveraging data for informed decision-making.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Nested subquery is not supported in the DELETE condition

diego_poggioli
Contributor

According to the documentation the WHERE predicate in a DELETE statement should supports subqueries, including INNOT INEXISTSNOT 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.

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

5 REPLIES 5

daniel_sahal
Esteemed Contributor

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

@daniel_sahal currently is a temp view

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

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

Thanks

Tejas2022
New Contributor II

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

 

 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group