Duplicated rows in MS Access database

Question

MS Access database was corrupted and in one table few rows was duplicated. They are absolutely same and there isn't any unique field between duplicates, even primary keys. Because of this, primary key was reseted from this table after repairing database. Now I can only know rows that were duplicated:

select * from tablename
where id in(
select id from tablename
group by id
having count (*) > 1)

To designate primary key I must delete one of two duplicates, but don't know how.

search SO: asked a gazillion times before. GBN had a good answer I seem to recall... — Mitch Wheat, Feb 10 '14 at 01:50
I saw this question, but as I understand it, there was a unique identifier different for each duplicate. In my case there is no such field. — Andrey Baulin, Feb 10 '14 at 02:21

score 1 · Accepted Answer · answered Feb 10 '14 at 01:51

One way you can do this is with a temporary table:

select distinct t.*
into TempTABLE
from tablename t;

delete from tablename;

insert into tablename
    select *
    from TempTable;

That is, remove the duplicates using distinct. Delete all the rows from the original table, and then insert the unique rows.

Duplicated rows in MS Access database

1 Answers1