Deleting Duplicate Rows in a Data Warehouse Table?

Hi russellhq ,

Based on your description, it appears that you are attempting a method to remove duplicates but are encountering errors when trying to execute the query. 

To effectively remove duplicates based on specific columns while avoiding errors, consider using the ROW_NUMBER() function in conjunction with a CTE. This method assigns each row in a dataset partition a unique row number based on a specified order. Any row within each partition with a row number greater than 1 can then be deleted, effectively removing duplicate rows.

If you are still encountering errors using this method, could you please provide more detailed information about the error messages you are receiving? It is also helpful to know the specific database system you are using (e.g. SQL Server, Azure SQL) as the syntax and functionality may vary.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thansk Anonymous .

The database I am using is a Fabric Data Warehouse. I'm using a CTE with ROW_NUMBER() as you describe, but keep getting an error. The error I get it:

WITH CTE AS (
        SELECT
            *,
            ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1 DESC) AS RowNum
        FROM
            dbo.duplicate_test
    )

    DELETE FROM CTE
    WHERE RowNum > 1 

 WITH CTE([Col1], 
    [DuplicateCount])
AS (SELECT [Col1],
           ROW_NUMBER() OVER(PARTITION BY [Col1]
           ORDER BY Col1) AS DuplicateCount
    FROM duplicate_test)
DELETE FROM CTE
WHERE DuplicateCount > 1 

 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
Msg 8624, Level 16, State 1, Code line 1 

mismatched input 'DELETE' expecting {'SELECT', '('} 

OK, I've managed to come up with a solution for this, I don't know if it's a very good one, but it seems to work for what I want it to do.

The solution was to write a stored procedure that creates a Temp table by selecting the distinct rows in the original table. Then it drops the original table, creates a new table using the original tables name and selects all records from the temp table, then drops the temp table.

The procedure looks like this:

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

CREATE TABLE [Excel Warehouse].[dbo].[TempDailyChecks] AS
SELECT DISTINCT *
FROM [DailyChecks];

DROP TABLE [DailyChecks];

CREATE TABLE [Excel Warehouse].[dbo].[DailyChecks] AS
SELECT *
FROM [TempDailyChecks];

DROP TABLE [TempDailyChecks];