Hi

I am trying to delete some duplicate rows, i have searched some of the previous posts on this and have developed this, but it does not quite work.

Basically, this is what i have is this table:

CandSkillID PK
CandID - FK
SkillID - FK

I need to delete records that match these criteria: if there are duplicate SkillID's that i related to a CandID, then delete all but the one (I am trying to leave the one with the highest CandSkillID)

i have developed this code but Currently it does not delete any rows, i believe the problem lies within the embedded SELECT statement, as it does not return rows when executed on its own.

Any help would be great,

Thanks Si


DELETE *
FROM tblCandAndSkill AS T
WHERE T.CandSkillID IN
(SELECT CandSkillID, SkillID, COUNT(*)
FROM tblCandAndSkill
WHERE CandID = 89431
GROUP BY CandSkillID, SkillID
HAVING COUNT(*) > 1)
AND CandSkillID <
(Select MAX(X.[CandSkillID])
FROM tblCandANDSkill As X
Where X.SkillID = T.SkillID
Group By X.SkillID);