VBStudent
asked on
SQL delete query for duplicate records in Access
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.
ASKER
it doesn't appear as though rowid is recognized in Access. Any other suggestions?
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes...rowId is autoNumbered field of yours...
ASKER
Thank you for your help
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
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
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...