What is the simplest way to delete records with duplicate name in a table? The answers I came across are very confusing.
Asked
Active
Viewed 2,767 times
3
-
Is there a key besides the name? Do you want to delete ALL or ALL but ONE? – n8wrl May 08 '09 at 19:39
-
Are they true duplicates in that every column of both rows is identical? – JohnOpincar May 08 '09 at 19:40
-
Also, what's the target DBMS? – JohnOpincar May 08 '09 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 May 08 '09 at 19:42
2 Answers
5
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
balexandre
- 73,608
- 45
- 233
- 342
Bryan
- 17,201
- 24
- 97
- 123
-
3
-
1I don't see a problem with that since answering even your own question can provide value to the community. – Tom H May 08 '09 at 20:18
-
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 May 11 '09 at 21:37
-
There's even a badge for answering your own question and getting votes. http://stackoverflow.com/badges/14/self-learner – BC. Jul 07 '10 at 22:05
-
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. – ahnbizcad Jun 22 '16 at 23:40
0
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
- 19,439
- 4
- 63
- 103