Deleting duplicates using a SQL query

[Kristen]

I need a fast way of deleting duplicate records.

This is what I have - an intersection table with 2 fields
(program_code, Key_code). Ideally the combination of these 2 should be
unique within the table. The table SHOULD have a primary key on it to
prevent duplicates, but alas, it does not.

So if I have 2 entries that both have a program_code of 12 and a
key_code of 100, I want to be able to delete the duplicate.

Any advice?

Thanks.

 

[Bill Mosca, MS Access MVP]

Quick and dirty way is to right-click the table and select copy. Then
right-click in the database window and select paste. You will get a box that
allows you to pick Structure Only. Name it something different from the
existing table.

Now open the new table in design view and add a primary key consisting of
your 2 fields. Save and close.

Append all records from the old table into the new one. You will get a
warning that not all records could be added due to key violations. Those
would be the dups.

Now rename your old table in case you need to go back to it. Give the new
table the old table's original name.

 

[Kristen]

Thanks Bill.

That is an option I guess but what I am trying to do is do this in VB
with ADOX. I am creating a database updater and I was hoping to just
put a PK on each table that has the duplicate problem. But I need to
delete the duplicates first.

I can do your approach and create a new table with a PK and then copy
the data from the old table, but I was hoping there was a simpler way.

 

[Bill Mosca, MS Access MVP]

Kristen

Do *did* say simple. I shy away from giving methods that have no safety net
when I am unsure of the poster's skill level. But if you are coding, I guess
I can relax.<g>

Here is a query that will delete dups:
DELETE *
FROM T_Data
WHERE (SELECT Count(*)
FROM T_Data As T1
WHERE (T1.F1 & T1.F2 & T1.F3 = T_Data.F1 & T_Data.F2 & T_Data.F3)
AND (T1.ID <= T_Data.ID)) > 1;

 

[Kristen]

Bill,

Thanks! This will require me to add an autonumber field called "ID"
first though. But thats easier than the other methods I was trying.

Yeah, I dont mind methods with no safety nets!

 

[Bill Mosca, MS Access MVP]

Kristen

You're very welcome.

ID needs to be an AutoNumber, in case you didn't realize that.