What is the simplest way to delete records with duplicate name in a table? The answers I came across are very confusing.
+2
A:
I got it! Simple and it worked great.
delete
t1
from
tTable t1, tTable t2
where
t1.locationName = t2.locationName and
t1.id > t2.id
Bryan
2009-05-08 19:40:52
answer your own question and get rep. interesting.
x0n
2009-05-08 19:50:13
I don't see a problem with that since answering even your own question can provide value to the community.
Tom H.
2009-05-08 20:18:26
I agree. Some users are caught up in a rep points competition. I could have not answered my question, but I wanted to save and share it for my own reference and for the community. This question has been asked before on stack overflow, but the answers are not as straight forward (most use cursors or for loops).
Bryan
2009-05-11 21:37:57
There's even a badge for answering your own question and getting votes. http://stackoverflow.com/badges/14/self-learner
BC
2010-07-07 22:05:59
A:
SQL Server 2005:
with FirstKey
AS
(
SELECT MIN(ID), Name, COUNT(*) AS Cnt
FROM YourTable
GROUP BY Name
HAVING COUNT(*) > 1
)
DELETE YourTable
FROM YourTable YT
JOIN FirstKey FK ON FK.Name = YT.Name AND FK.ID != YT.ID
n8wrl
2009-05-08 19:44:43