SQL Delete Duplicate Rows

Transact-SQL (TSQL) Quick Tips – SQL DELETE Duplicate Rows

I was recently compiling a distribution list (email) into a flat table.  The structure was simple with name and email address.  In some cases, a user was included on multiple email distribution lists, so they would show up multiple times in the table.  I know it would be proper to query the table prior to INSERT, dropping the record if it already exists, but I decided to take the alternative approach of deleting the duplicate records from the table, following the build.

The following SQL Statements create a temp table containing duplicate rows, then the SQL statement used to remove them.

 

 

Looking at the entire table below, you can see there are several duplicate rows (4, 8 and 9):

 

SQL Delete Duplicate Rows

 

Once the following statement is executed:

 

 

The duplicate rows are gone!

 

SQL Delete Duplicate Rows

 

Once again, there are certainly more sophisticated ways to prevent this situation from even happening, but sometimes you either inherit a table with duplicate rows or you need a quick and easy way to remove them.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment