Delete Duplicates - Leave one unique record behind

[NICKTHEGREEK]

Hi all

Is there anyway I can use an action query to automatically delete duplicate records and leave one unique record remaining? The duplicate values are only in one field. I have tried to use the find duplicates wizard, but this displays both occurances of the duplicated record, and if there are more than two occurances, it changes to a group by query and totals the number of duplications for each value, so then I can't delete any records! I don't care which record it deletes - as long as it leaves one unique record. ??? Any ideas???

Thanks,

Nick

 

[tlbroadbent]

There are several possible techniques. I like the method described at the following link.

http://support.microsoft.com/support/kb/articles/Q109/3/29.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[NICKTHEGREEK]

Is there any way to conditionally delete duplicate values by running a delete query? Eg. delete all duplicates (duplicated in field A) where Field B = 2??

 

[aplusc]

Try:

DELETE from myTable as t1 where exists (select * from myTable as t2 where t1.fieldA = t2.fieldA) AND FieldB = 2

Of course, you can add as many ANDs and ORs as you like.

P.S. This query can be painfully slow. I took me 27 hours to process 1,000,000 records, on 2GHZ machine!