Technology, Smartphones, Games

Remove Duplicate rows from table - SQL Server


I just came to a situation where I have duplicate entries in a table (all fields are identical) and I want to remove the duplicates. After a search I found a simple solution for my problem.

In my case, there were 2 duplicate entries for each record. To delete one record I just set ROWCOUNT to 1 and deleted records with a check of primary key


DELETE FROM tablename where id=PK

It will delete only one row. If you have n duplicate rows then you will have to set the ROWCOUNT to n-1 to remove all duplicates. Remember it till work if you have the same duplicate rows, otherwise set ROWCOUNT for each and use it.

After this you may Unset the ROWCOUNT by setting it to 0


It may help you Winking smile


You may also use

DELETE TOP (n) FROM <table name>

I have tried earlier this but with out those  braces (DELETE TOP n FROM <table name>) which showed error, but with those brackets it is working fine.

Thanks Amit  for taking time to comment this