Delete Duplicate Rows From a Table in SQL Server

Introduction

For every table, we use a primary key or unique key for distinct values, but sometimes we will encounter a problem with duplicate records. It is a major issue with duplicate rows. So we need to delete duplicate records to resolve it. So here I will explain how to delete duplicate rows.

Example:

Let's create one table, "employee", with 4 columns.

create table employee

(

empID int identity(1,1) primary key,

EmpNo int,

Name varchar(100),

Salary decimal(6,2)

)

Now we will insert some data into the "employee" table.

insert into employee(EmpNo, Name, Salary) values(101,'RAM',100)

insert into employee(EmpNo, Name, Salary) values(102,'RAJ',200)

insert into employee(EmpNo, Name, Salary) values(101,'RAM',100)

insert into employee(EmpNo, Name, Salary) values(102,'RAJ',200)

After executing the query above the table should be like this:

Table in Sql Server

So now I want to delete those duplicate rows.

Method 1

Here I want to delete duplicate records 3 and 4 from the employee table above. For this we can use the following query:

delete FROM dbo.employee WHERE empID NOT IN (SELECT MIN(empID) _
FROM dbo.employee GROUP BY EmpNo,Name,Salary)

Table in Sql Server1

Here it has deleted the EmpId records 3 and 4 .

Method 2

Here I want to delete duplicate records 1 and 2 from employee table above. For this we can use the following query. Here it will delete the minimum empid records.

delete FROM dbo.employee WHERE empID NOT IN (SELECT max(empID) _
FROM dbo.employee GROUP BY EmpNo,Name,Salary)

Table in Sql Server2

Here it has deleted EmpId records 1 and 2.