gigifarrow
asked on
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.S erial
FROM tblInductionCheckHistory
WHERE (((tblInductionCheckHistor y.Serial) In (SELECT [Serial] FROM [tblInductionCheckHistory] As Tmp GROUP BY [Serial] HAVING Count(*)>1 )));
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.S
FROM tblInductionCheckHistory
WHERE (((tblInductionCheckHistor
If you provide some sample data with tabe fields and desired result in excel format ... it will help :-)
ASKER
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
example;
serial numbers with duplicates
2334544
2334544
2334544
2468246
wants duplicates deleted it looks like this:
2334544
2468246
Helpdeleteduplicates.zip
ASKER
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]),[otherfiel dwithdupli cates]
from tblInductionCheckHistory
where [serial] not in(select min(a.[serial]),[otherfiel dwithdupli cates] from tblInductionCheckHistory
group by tblInductionCheckHistory.[ otherfield withduplic ates])
what is suppose to be in this field.?
What does the (a) mean in right here:(select min(a.[serial]),[otherfiel
from tblInductionCheckHistory
where [serial] not in(select min(a.[serial]),[otherfiel
group by tblInductionCheckHistory.[
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!
Works great!!
<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
in a query, you don't need to use DIM
you can use
from tableName AS T
or simply
from TableName T
delete *
from tblInductionCheckHistory
where [serial] not in(select min(a.[serial]),[otherfiel
group by tblInductionCheckHistory.[