Delete a single duplicate from a pair of duplicates SQL query

Bevos
Bevos used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
do you have a unique id field?

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

Commented:
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
Top Expert 2016

Commented:
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)
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

delete * from imported
where [id] not in (select min([id]) from imported as i where i.author2=Imported.Author2  and i.Volume=Imported.Volume)

Author

Commented:
This worked beautifully.  

Thank you so much,
Bevo

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial