3 weeks ago
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.
3 weeks ago
3 weeks ago
@diego_poggioli
What is the "source" in your WHERE statement? Is it a table or a view/temp view?
3 weeks ago - last edited 3 weeks ago
@daniel_sahal currently is a temp view
3 weeks ago
3 weeks ago
Yes, correct! using a table instead of a view fixed the error
Thanks
3 weeks ago - last edited 3 weeks ago
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)
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!