Delete duplicate data from table

Question

User779033375 posted

Hi All,

Can you please suggest a query that can delete all duplicate data from MS SQL table.

e.g IF there are >1 number of duplicate rows, then it should delete all >1 rows and remain 1.

I have written a query but, this will delete only one duplicate report.

delete from XTABLE where SL in(
    SELECT max(SL) SL FROM  XTABLE
                          GROUP BY    SL_TMP Having COUNT(*) > 1
                        )

Please suggest.

Answer 1

User603616845 posted

Hi,

You can use following query to delete the duplicate record but contain latest one.

Select * from @Table
;WITH CTE AS
(
	SELECT *,
	ROW_NUMBER() OVER(PARTITION BY Companyname ORDER BY imported) AS DuplicateCount
	FROM YourTableName
) Delete
FROM CTE
WHERE DuplicateCount > 1

You can also use some other way [just hints in MYSQL]

1) If you want to keep the row with the lowest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

2) If you want to keep the row with the highest id value:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

Hope this will help you.

thanks

Answer 2

User1724605321 posted

Hi saffy ,

Can you please suggest a query that can delete all duplicate data from MS SQL table.

Please refer to articles below for details and demo

#How to remove duplicate rows from a table in SQL Server

https://support.microsoft.com/en-us/kb/139444

#Removing Duplicates from a Table in SQL Server

https://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/ 

Hope it helps.

Best Regards,

Nan Yu

Answer 3

User779033375 posted

Thank you.

Will update status back ASAP.

Answer 4

User779033375 posted

Thank you for your answers