Deleting Duplicated Rows using CTE and getting "target DML table is not hash partitioned"

user_5961311 0
Viewed 768

We have a table with multiple columns and NO column ID. I am trying to delete duplicated rows when ALL columns are matched together. I found CTE to be helpful in this and managed to use it in our Azure SQL Server, but I am now getting the error on the same tables we have in our Synapse Pool:

The query processor could not produce a query plan because the target DML table is not hash partitioned.

I am using this structure of code to delete duplicated rows:

   WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY [col1], [col2], [col3], [col4], [col5], [col6], [col7] ORDER BY col1)
   FROM dbo.Table1
   )
   DELETE FROM CTE WHERE RN > 1
azure-sql-data-warehouse azure-synapse common-table-expression tsql sql
1 Answers

I haven't been able to get the "DELETE FROM CTE WHERE RN > 1" format to work with the Synapse Dedicated SQL Pool. You can accomplish what you are looking to do using the CTE and EXISTS:

WITH CTE AS(
    SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
        RN = ROW_NUMBER()OVER(PARTITION BY [col1], [col2], [col3], [col4], [col5], [col6], [col7] ORDER BY col1)
    FROM dbo.Table1
    )

DELETE FROM dbo.Table1 
WHERE EXISTS (
    SELECT *
    FROM CTE AS C
    WHERE dbo.Table1.[col1] = C.[col1]
    AND dbo.Table1.[col2] = C.[col2]
    AND dbo.Table1.[col3] = C.[col3]
    AND dbo.Table1.[col4] = C.[col4]
    AND dbo.Table1.[col5] = C.[col5]
    AND dbo.Table1.[col6] = C.[col6]
    AND dbo.Table1.[col7] = C.[col7]
    AND C.RN > 1
)

NOTE: I received syntax errors when giving dbo.Table1 an alias.

user_188675500
Related