Delete Duplicates with a twist

[Trainingjason]

Hi

I am training a course and have been asked the following question

I need to delete the duplicate data approx 170,000 records. However if Jason's has a duplicate with the data1 field with source and chic, I want to keep the chic duplicate. If data1 only has source or a chic and is not duplicated then the record is not to be deleted. Here is a sample table

Name Data1 Data2
Jason source 24/3/03
Jason chic 24/3/03
Tina source 21/3/03
Rebecca chic 21/3/03

Help


Jason

 

[dodgychris]

delete Table1.name, Table1.data1, Table1.data2
FROM Table1
WHERE (((Table1.name) In (SELECT [name] FROM [Table1] As Tmp GROUP BY [name] HAVING Count(*)>1 )))
and data1 = "source"

I think this should do it...

hope this helps, Dodgy Chris
-----------------------------------

confucious say : better to save a mans life than to build 7 storey pagoda