locked
Delete duplicate data from table RRS feed

  • 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.

    Friday, November 27, 2015 7:16 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 27, 2015 7:26 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 27, 2015 7:34 AM

All replies

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 27, 2015 7:26 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 27, 2015 7:34 AM
  • User779033375 posted

    Thank you.

    Will update status back ASAP.

    Friday, November 27, 2015 8:41 AM
  • User779033375 posted

    Thank you for your answers

    Wednesday, December 23, 2015 5:57 AM