Delete Duplicate Row From Table in SQL Server

In this blog, I will explain the procedure of deleting a duplicate row from your table using the Query.
 
 1. Script - Create One Table CityMaster
 
 CREATE TABLE [dbo].[CityMaster](
 [CityId] [int] IDENTITY(1,1) NOT NULL,
 [CityName] [varchar](50) NULL,
 )
 
 2. Add Some Duplicate Value In This Table and Run Your Query:
 
 Select * From CityMaster (Display All Records)


 
 3. Using This Query You Will Get The Distinct Value From Your Table
 

 Select distinct CityName from CityMaster

 

 

 
 4. Using This Query You Will Get The Duplicate Record In Your Table
 

 SELECT Cityname, ROW_NUMBER() OVER (PARTITION BY Cityname ORDER BY Cityname) AS Cityname FROM citymaster

 

 

 
 5. Finally, The Last Step To Remove The Duplicate Records From Your Table
 
 WITH DeleteDuplicate (Cityname, DuplicateCount)
 AS
 (
 SELECT Cityname, ROW_NUMBER() OVER(PARTITION BY Cityname ORDER BY Cityname) AS Cityname FROM CityMaster
 )
 DELETE
 FROM DeleteDuplicate
 WHERE DuplicateCount > 1

 GO

 

 

 
 Now, run your query once again:
 

Select * From CityMaster