Solved

SQL delete query for duplicate records in Access

Posted on 2008-10-31
9
1,278 Views
Last Modified: 2012-05-05
This is a follow up question to an earlier question.  I have an Access database with duplicate records.  The key is a composite of FUND, GROUP, ID.  How would I write a query to delete duplicate records in the table.
0
Comment
Question by:VBStudent
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22852809
Hi there;

DELETE FROM our_table WHERE rowid not in(SELECT MIN(rowid) FROM our_tabl eGROUP BY column1, column2, column3... ;

Here column1, column2, column3 constitute the identifying key for each record.

Best regards...
0
 

Author Comment

by:VBStudent
ID: 22853306
it doesn't appear as though rowid is recognized in Access.  Any other suggestions?
0
 
LVL 7

Expert Comment

by:UniqueData
ID: 22853830
don't award any points to me, just thought I would help out...

rowid is probably referring to a unique record id, usually an AutoNumber field.  I know you have the three fields that make up the key, but do you also have (or can you add) an AutoNumber?

Michael
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 9

Expert Comment

by:jamesgu
ID: 22855495
delete from <table>
inner join ( select FUND, GROUP, ID from <table> group by FUND, GROUP, ID having count(*) > 1) b on
<table>.FUND = a.FUND
<table>.GROUP = GROUP
<table>.ID = a.ID



0
 
LVL 9

Accepted Solution

by:
jamesgu earned 500 total points
ID: 22855516
delete from <table>
from <table>
inner join ( select FUND, GROUP, ID from <table> group by FUND, GROUP, ID having count(*) > 1) b on
<table>.FUND = a.FUND
<table>.GROUP = GROUP
<table>.ID = a.ID

--missed a from clause
0
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22856258
yes...rowId is autoNumbered field of yours...
0
 

Author Closing Comment

by:VBStudent
ID: 31512146
Thank you for your help
0
 

Expert Comment

by:sarcharan
ID: 23681243
I thank all of you for helping me. I found one more document to be quite interesting.

delete from t1 where tl.rowid >(select min(tl2.rowID) from t1 tl2 where tl.col1 = tl2.col1 and tl.col2 = tl2.col2)
====================
sp_rename 'dup_authors', 'temp_dup_authors'

select distinct *
into dup_authors
from temp_dup_authors

drop table temp_dup_authors
Deleting-Duplicate-Records.doc
0
 

Expert Comment

by:sarcharan
ID: 23681269
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question