[Resolved] Duplicate records - keep one only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Duplicate records - keep one only

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-04-12 : 08:36:44

I have a temp table with duplicate records (fullname) and I need to keep only one of the records that are duplicated. I tried below but that deletes all duplicate records


DELETE FROM #Table1 
WHERE fullname IN     
  (SELECT MIN(fullname) as [DeleteID]      
    FROM #Table1     
    GROUP BY fullname     
     HAVING COUNT(*) > 1     )

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-12 : 11:40:14

delete dt
from
(select row_number() over (partition by fullname order by fullname) as rn,* from #Table1)dt
where rn > 1

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2012-04-12 : 12:22:23

Purrfect, worked great. Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources