asked on 5/13/2011

Delete a single duplicate from a pair of duplicates SQL query

Hello, I have a query that finds duplicate values from my database but doesnt' do anything about them. I would like someone to show me how to change the code so that only a single record of the duplicates remains. I don't care which one, because these are all functionally identical entries.

Thanks,
Bevo

SELECT DISTINCT First(Imported.Author2) AS [Author2 Field], First(Imported.Year) AS [Year Field], First(Imported.TitleShrt) AS [TitleShrt Field], First(Imported.Volume) AS [Volume Field], Count(Imported.Author2) AS NumberOfDups
FROM Imported
GROUP BY Imported.Author2, Imported.Year, Imported.TitleShrt, Imported.Volume
HAVING (((Count(Imported.Author2))>1) AND ((Count(Imported.Volume))>1));

Open in new window

Microsoft Access MySQL Server

Last Comment

Bevos

5/17/2011

Rey Obrero (Capricorn1)

5/13/2011

do you have a unique id field?

delete * from tablex
where [id] not in(select min([id]) from tablex)

Rey Obrero (Capricorn1)

5/13/2011

the unique id field is the field [id] in the query posted above.

create a backup coy of the table before running the delete query

Rey Obrero (Capricorn1)

5/13/2011

missing some part in the query

delete * from tablex
where [id] not in(select min([id]) from tablex as A where A.Field1=tablex.field1 and A.field2=tablex.field2)

Bevos

5/13/2011

ASKER

Hi Capricorn1, the duplicate entries have different IDs (this is an autonumber field and they were imported afterward)

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

5/13/2011