How to delete duplicate rows in SQL Server?

by

I like CTEs and ROW_NUMBER as the two combined allow us to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE... to SELECT * FROM CTE:

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

DEMO (result is different; I assume that it’s due to a typo on your part)

COL1    COL2    COL3    COL4    COL5    COL6    COL7
john    1        1       1       1       1       1
sally   2        2       2       2       2       2

This example determines duplicates by a single column col1 because of the PARTITION BY col1. If you want to include multiple columns simply add them to the PARTITION BY:

ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)

More Related Contents:

  1. How can I delete using INNER JOIN with SQL Server?
  2. How do I find duplicates across multiple columns?
  3. How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table
  4. Efficiently convert rows to columns in sql server
  5. Conditional WHERE clause in SQL Server
  6. Multi-statement Table Valued Function vs Inline Table Valued Function
  7. SQL Server Insert if not exists
  8. Remove trailing zeros from decimal in SQL Server
  9. GROUP BY to combine/concat a column [duplicate]
  10. SQL Server – In clause with a declared variable [duplicate]
  11. How to set table name in dynamic SQL query?
  12. How to create composite primary key in SQL Server 2008
  13. How to turn IDENTITY_INSERT on and off using SQL Server 2008?
  14. Copy data into another table
  15. How do I see active SQL Server connections?
  16. EXISTS vs JOIN and use of EXISTS clause
  17. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  18. SQL Server 2008 – IF NOT EXISTS INSERT ELSE UPDATE
  19. Why is a UDF so much slower than a subquery?
  20. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  21. Query runs slow with date expression, but fast with string literal
  22. Alternate of lead lag function in SQL Server 2008
  23. UNIX_TIMESTAMP in SQL Server
  24. Generate all combinations in SQL
  25. Generate random int value from 3 to 6
  26. The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range?
  27. Add emoji / emoticon to SQL Server table
  28. How to execute a stored procedure inside a select query
  29. How to bulk insert only new rows in PostreSQL
  30. SQL Unpivot multiple columns Data

Leave a Comment