Forum Discussion
Deleting Duplicate Rows in a Data Warehouse Table?
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.
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.
After some more Googling, it looks like this may be a limitation of CTEs in a Fabric Data Warehouse
This stackoverflow post pointed me to this, and also gave a possible option to the OP on how to solve their issue, which was similar but not the same.
https://stackoverflow.com/questions/54624737/cte-with-delete-alternative-for-sql-data-warehouse
Is there a way to delete duplicates using a sub-query like the example in the stackoverflow post?