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