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 –

  1. Find duplicate rows.
  2. 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 Email 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 Email 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
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.