INF: Removing Duplicate Rows from an Existing Table in SQL

 Last reviewed: April 25, 1997
Article ID: Q70956

The information in this article applies to: 

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SUMMARY

The following script can be used to remove duplicate rows from a SQL Server table: 

   SELECT DISTINCT *
      INTO duplicate_table
      FROM original_table
      GROUP BY key_value
      HAVING COUNT(key_value) > 1

   DELETE original_table
      WHERE key_value
      IN (SELECT key_value
             FROM duplicate_table)

   INSERT original_table
      SELECT *
         FROM duplicate_table

     DROP TABLE duplicate_table
When executed, this script moves one instance of any duplicate row in the original table to a duplicate table. It then deletes all rows from the original table that also reside in the duplicate table. Next, the rows in the duplicate table are moved back into the original table. Finally, the duplicate table is dropped.

MORE INFORMATION

While this method is simple, it does require that you have enough space available in your database to temporarily build the duplicate table.

Additional query words: Windows NT
Keywords : kbprg SSrvTran_SQL
Version : 4.2 | 4.2
Platform : OS/2 Windows

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.