Delete duplicate entries (keep only one) (1 Viewer)

dlambert

Member
Local time
Today, 13:00
Joined
Apr 16, 2020
Messages
42
Hello all,
I have a table in my database that is accumulating duplicate entries. (i am trying to work out what is causing those duplicates as they should be prevented in the first place, for for now i need to be able to delete the duplicates)

The table is called tblJobs and has the following fields:
  • ID
  • JobName
  • (more)
I need to find duplicates based on JobName, and delete all extra entries (so i keep one of each, for example with the highest ID)

Any suggestions on how i can achieve this?
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:00
Joined
Sep 21, 2011
Messages
14,051
Use the Query Wizard to create Duplicates, call it Dupes.
Create another query grouping by the dupes and Max of ID. Call that Wanted.
Create your delete query WHERE Dupes.ID not in Wanted.?


Run it on test data first as well.
HTH
 

dlambert

Member
Local time
Today, 13:00
Joined
Apr 16, 2020
Messages
42
Use the Query Wizard to create Duplicates, call it Dupes.
Create another query grouping by the dupes and Max of ID. Call that Wanted.
Create your delete query WHERE Dupes.ID not in Wanted.?


Run it on test data first as well.
HTH
Hi Gasman,
Thanks for your suggestion, i think it will work. I am stuck on the last step.
How to a make the delete query WHERE Dupes.ID not in Wanted?
I have tried with no success...
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:00
Joined
Sep 21, 2011
Messages
14,051
I'm no SQL expert. :( and would usually do it in stages to check as well.
Create a third query that has something along the lines of
Code:
Select ID from Dupes WHERE ID NOT IN (SELECT ID FROM WANTED)

Call that DeleteRec
Then your final query would be
Code:
DELETE * WHERE ID IN (Select ID FROM DeleteRec)

It probably could be done by joins, but that is beyond me at present. :(
 

Isaac

Lifelong Learner
Local time
Today, 05:00
Joined
Mar 14, 2017
Messages
8,738
It probably could be done by joins, but that is beyond me at present
It's been a little while since I did this in SQL server, but I think the below is how .... and unsure to what extent Access will allow a correlated subquery but I think it will:
Code:
Delete * from table1 where table1.ID not in (select max(t2.ID) from table1 as t2 where t2.jobname=table1.jobname)

EDIT - just tested and it works.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:00
Joined
May 7, 2009
Messages
19,175
this one with the Lowest ID will be retained.
you can use Max() to retain the Highest ID.
Code:
DELETE *
FROM yourTableName
WHERE ID <>  (SELECT Min(ID) AS MinOfID FROM YourTableName AS Dupe    
   WHERE (Dupe.JobName = YourTable.JobName);
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:00
Joined
Feb 19, 2002
Messages
42,981
Once you have removed the duplicates, you need to add a unique index on the field JobName to prevent duplicates in the future.
 

Users who are viewing this thread

Top Bottom