Forum Discussion

russellhq's avatar
russellhq
Regular Visitor
2 years ago
Solved

Deleting Duplicate Rows in a Data Warehouse Table?

I've created a data warehouse which has a dataflow that connects to an excel file. Each week the excel file is updated but some of the old data is still in it, so when the dataflow runs and appends t...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Nothing to be proud of but if it works and doesn't break any GUIDs then so be it.

     

    Maybe add an index column to your data to avoid this in the future.

russellhq's avatar
russellhq
Regular Visitor
2 years ago

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:

 

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
 
For testing, I used a table called duplicate_test which has one column called Col1, with the values 1, 2, 3, 4, 5, 5, 3, 5
 
The queries I have tried are:

 

 

 
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
 

and

 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 

 

Both give the following error:

 

 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 

 

And in both, there is a red underline under DELETE, with the problem described as:

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