Delete Duplicates in MS SQL Server
Duplicate values in any table might be due to the poor table design or unwanted data from other sources. To delete the duplicate data from the table in SQL Server, follow the below steps –
- Find duplicate rows.
- Use DELETE statement to remove the duplicate rows.
Let us create a table named Geek –
CREATE TABLE Geek( Name NVARCHAR(100) NOT NULL, Email NVARCHAR(255) NOT NULL, City NVARCHAR(100) NOT NULL);
Let us insert some values into the table Geek –
INSERT INTO Geek (Name, Email, City) VALUES ('Nisha', 'nisha@gfg.com', 'Delhi'), ('Megha', 'megha@gfg.com', 'Noida'), ('Khushi', 'khushi@gfg.com', 'Jaipur'), ('Khushi', 'khushi@gfg.com', 'Jaipur'), ('Khushi', 'khushi@gfg.com', 'Jaipur'), ('Hina', 'hina@gfg.com', 'Kanpur'), ('Hina', 'hina@gfg.com', 'Kanpur'), ('Misha', 'misha@gfg.com', 'Gurugram'), ('Misha', 'misha@gfg.com', 'Gurugram'), ('Neha', 'neha@gfg.com', 'Pilani');
Let us display the contents of table Geek –
SELECT * FROM Geek;
Table – Geek
Name | City | |
---|---|---|
Nisha | nisha@gfg.com | Delhi |
Megha | megha@gfg.com | Noida |
Khushi | khushi@gfg.com | Jaipur |
Khushi | khushi@gfg.com | Jaipur |
Khushi | khushi@gfg.com | Jaipur |
Hina | hina@gfg.com | Kanpur |
Hina | hina@gfg.com | Kanpur |
Hina | hina@gfg.com | Kanpur |
Misha | misha@gfg.com | Gurugram |
Misha | misha@gfg.com | Gurugram |
Neha | neha@gfg.com | Pilani |
SQL Server query to delete duplicate records from the table Geek :
WITH CTE AS ( SELECT Name, Email, City ROW_NUMBER() OVER ( PARTITION BY Name, Email. City ORDER BY Name, Email. City ) row_num FROM Geek ) DELETE FROM CTE WHERE row_num > 1;
Output –
(5 rows affected)
SELECT * FROM Geek;
Table – Geek
Name | City | |
---|---|---|
Nisha | nisha@gfg.com | Delhi |
Megha | megha@gfg.com | Noida |
Khushi | khushi@gfg.com | Jaipur |
Hina | hina@gfg.com | Kanpur |
Misha | misha@gfg.com | Gurugram |
Neha | neha@gfg.com | Pilani |
Contact Us