Delete All Duplicate Rows or Records from a Table in SQL Server

In this blog, you will learn how to delete all duplicate rows or records from a table in SQL Server

Introduction

 

In this article, you will learn how to delete duplicate rows from a table in SQL Server using the CTE function.

 

SQL query to delete duplicate rows using CTE

 

Let’s create a sample table for the demonstration.

 

 

There are many duplicate rows for Student marks with the same student name and marks.

 

In this statement

WITH CTE AS
(
   Select ROW_NUMBER() Over(Partition by StudentName, SubMarks Order by StudentId) as 'RowNumber',*
   from StudentMark
)
Delete from CTE Where RowNumber > 1

• First, the CTE uses the ROW_NUMBER() function to find the duplicate rows specified by values in the student name and marks columns.
• Then, the DELETE statement deletes all the duplicate rows but keeps only one occurrence of each duplicate group.

 

Summary

 

In this article, we have learned how to delete duplicate rows from a table in SQL Server.