Delete Duplicate Records

Question

Hi

I need some help getting a script out to delete duplicate records in my database table.

Take the following example scenario :

Table Structure

Artist VARCHAR(100)
Album VARHCAR(100)
Title VARCHAR(100)
Duration int
DateAdded DATETIME

The duplicated fields are the artist, album and title, the DateAdded field of the duplicated records could be out by up to 30 seconds and the Duration can be out by 2 seconds.

Any help would be appreciated!

Thanks

Answer 1

Here is one way to keep the most current record and delete the rest(SQL 2005 and up)

;WITH CTE AS
(
	SELECT Artist,Album,Title,Duration
	,DateAdded,ROW_NUMBER() OVER(PARTITION BY Artist,Album,Title ORDER BY DateAdded DESC,Duration DESC) AS Row
	FROM TableStructure
)
  DELETE FROM CTE WHERE Row > 1

---

Abdallah, PMP, MCTS

Answer 2

The simplest DEDUP is SELECT INTO table create, followed by update of the values you want to keep for Duration & Dateadded. Start with renaming the table zzzMusicAlbum (to be deleted in the future).

SELECT   Artist, 
         Album, 
         Title, 
         Duration = MIN(Duration), 
         DateAdded = MIN(DateAdded) 
INTO     MusicAlbum 
FROM     zzzMusicAlbum 
GROUP BY Artist, 
         Album, 
         Title 
ORDER BY Artist, 
         Album, 
         Title 

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com