Delete duplicates in table - SQL?

[news2]

Is there a "neat" way to delete records which are duplicates as defined as
equal in a particlular field. I can do it with VBA but wonder whether it
can be done in SQL.

 

[Guest]

It's pretty easy IF you have a primary key assigned for that table AND you
really, really know which records that you don't want to keep. For example
you define a duplicate as having the same data in a field but other fields
have different data. Which record stays and which record(s) get deleted?

On the other hand if a record is completely duplicated in every field AND
there are no relationships with referiential integrity enforced with other
tables, you can create a DISTINCT query; turn it into a make table query and
run it; delete the original table; then rename the new table the same as the
old.

Maybe show us some representative data so that we can see what you have.

 

[news2]

....

Maybe show us some representative data so that we can see what you have.

The situation is that people have been selected for processing based on
various criteria which results in overlap such that the same person may
appear multiple time. The objective is to have each selected individual
appear only once in the table.

M is a recordset, X an integer, MemNo is a unique individual identifier
(auto-number).
==========

' Go through the records, deleting all but the first of a given MemNo.
M.MoveFirst
X = M!MemNo
M.MoveNext
While Not M.EOF
If M!MemNo = X And M!TYPE <> "G" Then
M.Delete
End If
M.MoveNext
Wend
===========

I can live with this code, but I just thought it might be nice if there were
a clearer representation of the process if it were in SQL.

Dick Cleaveland
..