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: 

Permission Error When Running DELETE FROM

Carsten03
New Contributor III

Hi,

I want to remove duplicate rows from my managed delta table in my unity catalog. I use a query on a SQL warehouse similar to this:

 

 

WITH cte AS (
SELECT 
id, ROW_NUMBER() OVER (PARTITION BY id,##,##,## ORDER BY ts) AS row_num
FROM 
    catalog.schema.table
) DELETE FROM cte WHERE row_num > 1;

 

 

Using this I get following error:

 

 

Could not verify permissions for DeleteFromTable (row_num#734 > 1)

 

 

I checked the grants where I have ALL PRIVILEGES and the IAM Role (we are on AWS) for the external location and I have permissions to delete from S3. Is there anything else I have to set to be able to delete rows or is there anything wrong with my query? I couldn't find anything online.

 

 

2 REPLIES 2

Carsten03
New Contributor III

I have first tried to use _metadata.row_index to delete the correct rows but also this resulted in an error. My solution was now to use spark and overwrite the table.

table_name = "catalog.schema.table"
df = spark.read.table(table_name)
count_df = df.count()
dedup = df.drop_duplicates()
count_dedup = dedup.count()
count_df - count_dedup
dedup.write.mode("overwrite").saveAsTable(table_name)
assert spark.read.table(table_name).count() == count_dedup

I would still be interested if anyone has a good approach using SQL for this.

ac0
New Contributor III

Pitching in here since i had the same problem: the issue you can't delete from a CTE; you need to delete from the actual table which can be joined to the CTE.

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!