Delete duplicate rows

[SimonFinn]

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, i have CandSkillID is the PK for this table, i need to delete records that have duplicate SkillID, and where the CandID = 89431.

Currently it does not delete any rows, i believe the problem lies within the embedded SELECT statement, because that does not return rows when executed on its own.

Thanks Si

Delete *
From tblCandAndSkill AS T
WHERE T.CandSkillID IN
(SELECT CandSkillID, SkillID
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)

 

[Golom]

The first thing that jumps out is that an IN clause must reference a single column (i.e. your select must return exactly one column) and yours returns two (CandSkillID, SkillID). Access/JET usually raises an error when you do this.

Second ... if &quot;CandSkillID&quot; is the primary key then, by definition, it is unique and there cannot be any records where &quot;HAVING Count(*) > 1&quot;. That would require that both &quot;CandSkillID&quot; and &quot;SkillID&quot; had the same values on two or more different records. Should you have

    GROUP BY CandID, SkillID

instead?

 

[SimonFinn]

Thanks for that, solved the problem.

I now have this statement in VBA, it works fine but; is there a quicker way of running this statement, as it takes an exceptionally long time using ADO.

cnBackEnd.Execute
&quot;DELETE *
FROM tblCandAndSkill AS T
WHERE T.CandSkillID IN
(SELECT CandSkillID
From tblCandANDSkill
Where CandID = &quot; & candidateID & &quot;
GROUP BY CandSkillID, SkillID)
AND CandSkillID <
(Select MAX(X.[CandSkillID])
FROM tblCandANDSkill
As X Where X.skillID = T.skillID
Group By X.SkillID);&quot;