CODEWITHSUNDEEP
sqlms-access
sigil
sigil

Reputation: 9546

How to delete duplicate records in SQL that differ in a single field?

I'm using Access 2007.

I have a table with about 20 fields. One of those fields is an autonumber ID, so it's unique. I have a lot of records in this table that only differ in their autonumber ID. I can't just delete the ones with odd or even IDs, because some pairs of duplicates have both odds or both evens. Any ideas on how to select one record from each pair for deletion?

I know this could probably be done with VBA, but I'm not really familiar with Access VBA yet, so I'm looking for a purely SQL-based solution.

Upvotes: 1

Views: 2977

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

DELETE *
FROM yourTable
WHERE id NOT IN
  ( SELECT min(id)
    FROM yourTable
    GROUP BY field2
           , filed3
           , field4
           , etc...      <--- all other fields, except id
  )

Upvotes: 3

Randy
Randy

Reputation: 16673

delete the ones that are not min(id), grouped by the other columns that mean equivalence.

Upvotes: 2

Related Questions