It may happen times to times you need to delete duplicate rows from a table. There are many options to do that. I will write on my blog one of my option that I am using often to remove duplicate rows from a table.
Let’s say we have a table called tblPersons and the columns: PersonalNumber, Name, Surname, Phone. The table doesn’t have any primary key, so it may happen that we have inserted more than once a same person with the same PersonalNumber.
CREATE TABLE tblPersons( PersonalNumber nvarchar(50), Name nvarchar(128), Surname nvarchar(128), Phone nvarchar(50));
Now, the task is to remove all rows from tblPersons that have same PersonalNumber and in the end to have uniqe PersonalNumber in the table. The query is as follow:
;WITH numbers AS (SELECT PersonalNumber, ROW_NUMBER() OVER(PARTITION BY PersonalNumber ORDER BY PersonalNumber) AS pn FROM tblPersons) DELETE FROM numbers WHERE pn > 1