delete query, delete duplicates but keep one (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 19:27
Joined
Jan 14, 2017
Messages
18,186
jdraw method is the simpliest.

When i post vba against sql some wise guy react. now sonebidy post vba, approved without thinking?! Who are you?

Both jdraw's method and mine work well.
The only reason I prefer mine is that there is no need to create a temp table.

As for the second sentence arnelgp, the recent comments were about you using VBA functions unnecessarily when a simpler solution is available.
Nobody objects to the use of VBA when that is the best solution.

And what are you on about here....
...approved without thinking?! Who are you?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:27
Joined
May 7, 2009
Messages
19,169
As for the second sentence arnelgp, the recent comments were about you using VBA functions unnecessarily when a simpler solution is available.
Nobody objects to the use of VBA when that is the best solution.

Look at my Sql post, is that not a simple solution without unnecessary vba?
 

isladogs

MVP / VIP
Local time
Today, 19:27
Joined
Jan 14, 2017
Messages
18,186
If your sql code works that's great.

I haven't tested it myself as I have no duplicates needing removal.
However, S_Preston gave a reason (max locks) for not using it.

Whilst that reason is solvable, you haven't explained what your comment was on about ....
 

isladogs

MVP / VIP
Local time
Today, 19:27
Joined
Jan 14, 2017
Messages
18,186
@arnelgp
I've just tested your generic duplicates delete query on a small data sample I made specially for testing and can confirm it worked perfectly.
The first record of each duplicate set is retained, all others deleted

I see no reason why it shouldn't work for large datafiles as well though I haven't tested it
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:27
Joined
Feb 19, 2002
Messages
42,973
The simplest solution for eliminating duplicates is:
1. Create a new empty table.
2. Add the necessary unique indexes to PREVENT duplicates.
3. Create an append query to select the old data and append it to the new table. Sort this query to get the record you want to save to sort first.

At the end of the append, you will get an error message telling you how many duplicates were ignored.
 

Mark_

Longboard on the internet
Local time
Today, 12:27
Joined
Sep 12, 2017
Messages
2,111
@ Preston,

Can you shed a little light on your issue, namely how you came to have 3 million records with enough duplicate data to warrant this? Helping fix the data after the fact leaves you with the issue of how duplicate entries were put in originally.
 

Users who are viewing this thread

Top Bottom