[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Delete Duplicate query. Deletes duplicates. But I need for it to keep one record once it deletes

Hello, Experts
I have a query that finds the duplicates and deletes them. This works fine. However, it deletes all the duplicates of a certain record. I need for it once it finds a duplicate delete it but just keep one of the records.

Meaning delete all but one, once you find the duplicates.

DELETE [Serial ].*, tblInductionCheckHistory.Serial
FROM tblInductionCheckHistory
WHERE (((tblInductionCheckHistory.Serial) In (SELECT [Serial] FROM [tblInductionCheckHistory] As Tmp GROUP BY [Serial] HAVING Count(*)>1 )));
0
gigifarrow
Asked:
gigifarrow
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
are you deleting the duplicates except one in tblInductionCheckHistory ?


delete *
from tblInductionCheckHistory
where [serial] not in(select min(a.[serial]),[otherfieldwithduplicates] from tblInductionCheckHistory
group by tblInductionCheckHistory.[otherfieldwithduplicates])
0
 
RehanYousafCommented:
If you provide some sample data with tabe fields and desired result in excel format ... it will help :-)
0
 
gigifarrowAuthor Commented:
Okay here is the table and the query it deletes all the records that have duplicates. I need for it to delete all but one.




example;

serial numbers with duplicates
2334544
2334544
2334544
2468246

wants duplicates deleted it looks like this:
2334544
2468246
Helpdeleteduplicates.zip
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
gigifarrowAuthor Commented:
I dont understand where it says [otherfieldwithduplicates]
what is suppose to be in this field.?

What does the (a) mean in right here:(select min(a.[serial]),[otherfieldwithduplicates]

from tblInductionCheckHistory
where [serial] not in(select min(a.[serial]),[otherfieldwithduplicates] from tblInductionCheckHistory
group by tblInductionCheckHistory.[otherfieldwithduplicates])
0
 
Rey Obrero (Capricorn1)Commented:
you need to add an autonumber field to your table

see this revised db

see query1
Helpdeleteduplicates.accdb
0
 
gigifarrowAuthor Commented:
Thank you for your help how come you dont have to Dim a and b. If you have time could you please explain. Just want to understand. So I can do it on my own.

Works great!!
0
 
Rey Obrero (Capricorn1)Commented:
<how come you dont have to Dim a and b>

in a query, you don't need to use DIM

you can use

from tableName AS T

or simply

from TableName  T
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now