Deleting Duplicates - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Deleting Duplicates

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-28 : 07:44:39

Hello there

I know this is a topic that has been resolved many a time on this forum, and I apologise for bringing it up again. Unfortunately, for all the examples I have seen, I still have not been able to re-work my example below to delete all duplicates except the earliest, (perhaps due to an overly excessive weekend). I have a table:

TABLE LetterRepository
______________________________
RepositoryID IDENTITY INT PK
MailDate SMALLDATETIME
CycleInstanceID INT FK
LetterCodeID INT FK
A000_Document VARCHAR(2000)

There are duplicate values in the table, but with different PK's:

RepositoryID MailDate CycleInstanceID LetterCodeID A000_Document
_____________________________________________________________________
1 2 March 2005 28 3 hello
2 2 March 2005 28 3 hello
3 2 March 2005 28 3 hello

I want to delete all but the earliest duplicate (the one with the lowest IDENTITY value). The code below deletes all the duplicates rather than leaving one remaining:

DELETE FROM T1
FROM LetterRepository T1
RIGHT OUTER JOIN
(SELECT DISTINCT A000_Document, MailDate, R.CycleInstanceID, LetterCodeID
FROM LetterRepository R
INNER JOIN CycleInstance C ON C.CycleInstanceID = R.CycleInstanceID
GROUP BY A000_Document, MailDate, R.CycleInstanceID, LetterCodeID
HAVING COUNT(*) > 1
AND R.CycleInstanceID = @cycleInstanceID
) AS T2
ON T1.A000_Document = T2.A000_Document
AND T1.MailDate = T2.MailDate
AND T1.CycleInstanceID = T2.CycleInstanceID
AND T1.LetterCodeID = T2.LetterCodeID

How can I rework the above so a row remains???

Hearty head pats

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-28 : 09:27:56

This should help:

http://weblogs.sqlteam.com/jeffs/archive/2004/10/07/2190.aspx

let me know if you have any questions.

- Jeff

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-28 : 09:56:52

quote:
Originally posted by jsmith8858

This should help:

http://weblogs.sqlteam.com/jeffs/archive/2004/10/07/2190.aspx

let me know if you have any questions.

- Jeff

Jeff, I gotta hand it to you, that's the best darn write-up I've seen on the subject!

Thanks for contributing to my understanding

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Bex
Aged Yak Warrior

580 Posts

Posted - 2005-02-28 : 11:26:53

Hi Jeff, thanks for the link. I still have not yet managed to solve the problem. I don't need to resolve any foreign keys, just simply delete the duplicates. Sorry for being a dunce, but I still don't get it. I have to confess, my SQL writing skills are very poor, and I have been having problems with understanding nested selects, etc. I think it is too late in the day to get anywhere, so I shall try and pick it up again tomorrow. Maybe I will have more luck when feeling a bit fresher!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources