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

isladogs

CID VIP
Local time
Today, 04:30
Joined
Jan 14, 2017
Messages
14,229
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

error reading drive A:
Local time
Today, 12:30
Joined
May 7, 2009
Messages
11,121
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

CID VIP
Local time
Today, 04:30
Joined
Jan 14, 2017
Messages
14,229
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

CID VIP
Local time
Today, 04:30
Joined
Jan 14, 2017
Messages
14,229
@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
Yesterday, 23:30
Joined
Feb 19, 2002
Messages
30,570
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
Yesterday, 20:30
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