Skip to content
Related Articles

Related Articles

Improve Article
Delete Duplicates in MS SQL Server
  • Last Updated : 08 Sep, 2020

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



NameEmailCity
Nishanisha@gfg.comDelhi
Meghamegha@gfg.comNoida
Khushikhushi@gfg.comJaipur
Khushikhushi@gfg.comJaipur
Khushikhushi@gfg.comJaipur
Hinahina@gfg.comKanpur
Hinahina@gfg.comKanpur
Hinahina@gfg.comKanpur
Mishamisha@gfg.comGurugram
Mishamisha@gfg.comGurugram
Nehaneha@gfg.comPilani



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

NameEmailCity
Nishanisha@gfg.comDelhi
Meghamegha@gfg.comNoida
Khushikhushi@gfg.comJaipur
Hinahina@gfg.comKanpur
Mishamisha@gfg.comGurugram
Nehaneha@gfg.comPilani
My Personal Notes arrow_drop_up
Recommended Articles
Page :