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