What is the simplest way to delete records with duplicate name in a table? The answers I came across are very confusing.
-
Is there a key besides the name? Do you want to delete ALL or ALL but ONE?n8wrl– n8wrl2009-05-08 19:39:53 +00:00Commented May 8, 2009 at 19:39
-
Are they true duplicates in that every column of both rows is identical?JohnOpincar– JohnOpincar2009-05-08 19:40:21 +00:00Commented May 8, 2009 at 19:40
-
Also, what's the target DBMS?JohnOpincar– JohnOpincar2009-05-08 19:40:55 +00:00Commented May 8, 2009 at 19:40
-
There was a foreign key and I had to remove it. They were not identical rows and had a unique primary key 'id'Bryan– Bryan2009-05-08 19:42:24 +00:00Commented May 8, 2009 at 19:42
Add a comment
|
2 Answers
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
5 Comments
x0n
answer your own question and get rep. interesting.
Tom H
I don't see a problem with that since answering even your own question can provide value to the community.
Bryan
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).
BC.
There's even a badge for answering your own question and getting votes. stackoverflow.com/badges/14/self-learner
ahnbizcad
you don't get rep for answering. but if other people find it helpful, then you have actually helped others, so i'd say it's deserved.
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
