delete dups keep lowest row autonum 1

[rwn]

Any idea what is wrong with this sql statement. I have a row called LOTNUM_72 and want to delete all duplicates and keep only one record of each duplicates. The record that is kepted will be the one with the lowest autonumber. The auto numbered row is call ID.



Delete From [LotTrackingHistoryD]Where ID No In (Select Mn(ID) as MinID from FROM [Find duplicates for(LOTNUM_72) ] Group BY LOTNUM_72)

 

[Golom]

Try

Delete * From [LotTrackingHistoryD][COLOR=red yellow] [/color]
Where ID NO[COLOR=red]T[/color] In 
(Select Mn(ID) as MinID from 
 FROM [Find duplicates for(LOTNUM_72)]
  Group BY LOTNUM_72)

I assume that the query [Find duplicates for(LOTNUM_72)] contains a list of those records that have duplicates and that there are other records in the table that are not duplicated and therefore don't appear at all in [Find duplicates for(LOTNUM_72)] .

This SQL will delete ALL records that are NOT duplicated AND ALL but the MIN(ID) for those that are.

Is that what you want?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[JerryKlmns]

Delete * From [LotTrackingHistoryD]
Where ID NOT In
(Select M[red]i[/red]n(ID) as MinID [s]from[/s]
 FROM [Find duplicates for(LOTNUM_72)]
 Group BY LOTNUM_72)

 

[rwn]

The delete query works great. But since I have about 150000 records for it to search on the query is very slow. I do have a field in my table that is for a date called TDate. Is there anyway of having this delete query include a date range of current date to minus 10 days and only looks for duplicates within that date range??

Thanks for the help. rwn

 

[PHV]

A faster way:
DELETE FROM LotTrackingHistoryD
WHERE ID Not In (SELECT Min(ID) FROM LotTrackingHistoryD GROUP BY LOTNUM_72)

For the date range:
DELETE FROM LotTrackingHistoryD
WHERE (TDate Between Date()-10 And Date())
AND ID Not In (SELECT Min(ID) FROM LotTrackingHistoryD WHERE (TDate Between Date()-10 And Date()) GROUP BY LOTNUM_72)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rwn]

This all worked great. I would like to modify the delete query so it still deletes the most recent entry of a duplicate, but ONLY if the lab table.qty is an exact match. So if a lot number is entered twice and a qty of 100 exists and 200 exists, for the same lot number, then no delete is perform. Any suggestions??


sSQL = "DELETE [Lab Table].TDate, [Lab Table].ID, * FROM [Lab Table] WHERE ((([Lab Table].TDate) Between Date()-5 And Date())" & _
" AND (([Lab Table].ID) Not In (SELECT Min(ID) as MinID FROM [Lab Table] WHERE ([Lab Table].TDate Between Date()-5 " & _
"And Date()) GROUP BY [Lab Table].LotNumber)));"