Technology, Smartphones, Games


Remove Duplicate rows from table - SQL Server

image

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

SET ROWCOUNT 1

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

SET ROWCOUNT 0

It may help you Winking smile

Update

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