How To Find and Remove Duplicate records in SQL ?

---

I have created a table and inserted some records in Table for understanding purposes.

 

•  Creating New Table

create table Employee
(
ID int ,
Name varchar(20),
Salary float,
Dept_Name varchar(20)
)

 

•  Inserting data in table

Insert into Employee(id,Name,Salary,Dept_Name)values(1,'Alice',20000,'HR')
Insert into Employee(id,Name,Salary,Dept_Name)values(2,'Bob',10000,'Finance')
Insert into Employee(id,Name,Salary,Dept_Name)values(3,'Camlie',28000,'IT')
Insert into Employee(id,Name,Salary,Dept_Name)values(4,'Don',15000,'Sales')
Insert into Employee(id,Name,Salary,Dept_Name)values(5,'Elli',39000,'HR')
Insert into Employee(id,Name,Salary,Dept_Name)values(6,'Fukale',12000,'Finance')
Insert into Employee(id,Name,Salary,Dept_Name)values(7,'Govin',40000,'IT')
Insert into Employee(id,Name,Salary,Dept_Name)values(8,'Halios',32000,'Finance')
Insert into Employee(id,Name,Salary,Dept_Name)values(9,'Idomnes',56000,'IT')
Insert into Employee(id,Name,Salary,Dept_Name)values(10,'Jacob',29000,'Communication')
Insert into Employee(id,Name,Salary,Dept_Name)values(10,'Jacob',29000,'Communication')
Insert into Employee(id,Name,Salary,Dept_Name)values(10,'Jacob',29000,'Communication')
Insert into Employee(id,Name,Salary,Dept_Name)values(10,'Jacob',29000,'Communication')

 

• Populate the data through Select Statement

Select * from Employee order by ID

 

• SQL query to fetch duplicate records in a table.

 

SELECT Id,Name, Salary, COUNT(*)
FROM Employee
GROUP BY Id,Name, Salary
HAVING COUNT(*) > 1;

 

 

 

 

 

 

• SQL query to remove duplicates from a table

 

DELETE FROM Employee
WHERE ID IN (
SELECT ID
FROM Employee
GROUP BY id,Name,Salary,Dept_Name
HAVING COUNT(*) >1)

 

 

 

 

 

 

 

• Records After duplicates Removal

Select * from Employee order by ID

 

 

---