Delete duplicate rows in a large table

September 1, 2022

I have a table with 50+ columns that contains duplicate rows which I would like to delete. I would normally use the ROW_NUMBER function, however I don’t know the unique combination of columns in my table to put in the partition by clause. Is there a way to delete duplicate rows in a table without typing out all the column names?

>Solution :

Since you use ROW_NUMBER i assume you use MS SQL-Server. You can select all columns easily in SSMS. Go to your table and drag the Columns folder under it to the editor. Now you have all columns comma separated. You just need to copy&paste it into this query:

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