Another duplicate delete question...

[DAW]

Have searched the forum but cannot find a suitable answer...

I have duplicates in my table that I need to delete but leave one existing record, e.g.:

ID Part Family Colour Cost
1 123456 HHH M98765 12.99
2 123456 HHH M64975 12.99
3 123456 HHH E79461 12.99
4 123456 HHH G13467 12.99
5 123456 HHH M98765 12.99

In the above example i would want to delete records 2-5 (based on Part and Family, even though the colours are different) leaving one there. If I run the find duplicates and then change that to a delete query then it deletes them all.

This needs to be done via a query as it is done in automation, so manually copying the table structure and applying primary keys to the fileds etc. is not an option.

Any bright ideas?

 

[ByteMyzer]

ID Part Family Colour Cost
1 123456 HHH M98765 12.99
2 123456 HHH M64975 12.99
3 123456 HHH E79461 12.99
4 123456 HHH G13467 12.99
5 123456 HHH M98765 12.99

Try this (change MyTable in both places to the name of your actual table):

DELETE T1.*
FROM [b][MyTable][/b] T1
WHERE T1.ID>
(SELECT MIN(T2.ID)
FROM [b][MyTable][/b] T2
WHERE T2.Part=T1.Part
AND T2.Family=T1.Family);

See if this works for you.

 

[DAW]

Well, rodger me with a fish fork! That works perfectly.
The only thing is that i don't undertand exactly how it is achieving the goal. If someone has a few minutes maybe they could help the learning along?...