Delete duplicate records in SQL Server?

by

You can do this with window functions. It will order the dupes by empId, and delete all but the first one.

delete x from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;

Run it as a select to see what would be deleted:

select *
from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;

More Related Contents:

  1. Removing duplicate rows from table in Oracle
  2. Removing duplicate rows (based on values from multiple columns) from SQL table
  3. Calculate a Running Total in SQL Server
  4. Truncate (not round) decimal places in SQL Server
  5. How to return multiple values in one column (T-SQL)?
  6. Dynamically create columns sql
  7. Reference alias (calculated in SELECT) in WHERE clause
  8. When to use Common Table Expression (CTE)
  9. INT vs Unique-Identifier for ID field in database
  10. How to send email from SQL Server?
  11. How do I sort a VARCHAR column in SQL server that contains numbers?
  12. TSQL – Cast string to integer or return default value
  13. How To Create Table with Identity Column
  14. How to pivot rows into columns (custom pivoting)
  15. Why do you create a View in a database?
  16. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  17. How to split string using delimiter char using T-SQL?
  18. Calculate the number of records for each date between 2 dates
  19. T-SQL: How to use parameters in dynamic SQL?
  20. How to compare datetime with only date in SQL Server
  21. Merge overlapping date intervals
  22. SQL server select distinct rows using most recent value only
  23. Generate all combinations in SQL
  24. Split words with a capital letter in sql
  25. SQL poor stored procedure execution plan performance – parameter sniffing
  26. How to search SQL column containing JSON array
  27. Eliminate and reduce overlapping date ranges
  28. scope_identity vs ident_current
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment