Bujar Ademi

Bujar Ademi

Windows Programmer

SQL query remove duplicate rows from table
Bujar Ademi

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