Solved

SQL delete query for duplicate records in Access

Posted on 2008-10-31
9
1,274 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…

947 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now