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)

 

 

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!