Delete duplicate fields in a table

[Cloonalt]

I have a table with a field that has duplicate values. The rest of the record may not be unique.

So, for instance, if my table has two (or more) values of Mary in a field called FirstName, I want to leave only one of them, even if the address is not the same. (This is just an example)
It doesn't matter which ones get deleted. Can I do that in a SQL statement?

Thanks.

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=5721

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

 

[Cloonalt]

PHV, that looks like it will work. However, I execute

DELETE DCN
FROM Daily_Dat
WHERE ID Not In (SELECT Max(ID) FROM Daily_Dat GROUP BY DCN)

and get a message "Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'DCN'."

Thanks for your help!

 

[TheAceMan1]

How are ya Cloonalt . . .

. . . and if you include the square brackets [blue][][/blue] ?

See Ya! . . . . . .

 

[PHV]

And this ?
DELETE FROM Daily_Dat
WHERE ID Not In (SELECT Max(ID) FROM Daily_Dat GROUP BY DCN)

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