delete duplicates


R

Robbie

I made a find duplicates query using the wizard, but now I need to delete
the duplicate records. I don't care which one is deleted so long as only one
exists. How can I turn this query into a delete query that will remove the
extra records?

SELECT Products.ISBN, Products.Title, Products.Binding, Products.ISBN13,
Products.ISBN10
FROM Products
WHERE (((Products.ISBN) In (SELECT [ISBN] FROM [Products] As Tmp GROUP BY
[ISBN] HAVING Count(*)>1 )))
ORDER BY Products.ISBN;


Thanks
 
Ad

Advertisements

J

Jeff Boyce

Robbie

One approach might be to first select unique values, then create a new table
that only has those.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
R

Robbie

So you are saying that I can't delete duplicates so I should rebuild my
table instead?

Jeff Boyce said:
Robbie

One approach might be to first select unique values, then create a new
table that only has those.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Robbie said:
I made a find duplicates query using the wizard, but now I need to delete
the duplicate records. I don't care which one is deleted so long as only
one exists. How can I turn this query into a delete query that will remove
the extra records?

SELECT Products.ISBN, Products.Title, Products.Binding, Products.ISBN13,
Products.ISBN10
FROM Products
WHERE (((Products.ISBN) In (SELECT [ISBN] FROM [Products] As Tmp GROUP BY
[ISBN] HAVING Count(*)>1 )))
ORDER BY Products.ISBN;


Thanks
 
C

Cheese_whiz

Hi Robbie,

Here's the basic technique.

http://support.microsoft.com/kb/209183

Two additional comments:

1. I've seen some people, apparently out of an (over?)abundance of caution,
suggest you make TWO copies of the original table (one a copy of just the
structure, and the other a copy of both structure/data) and, thereafter, work
just on the copies (until the end where you delete the original). I'm
paranoid, so I probably would do it that way, but I'd also backup the back
end before starting anyway.

2. The article doesn't say it, but I think you need to, after you've done
the appending and you are sure it's correct, go back into the new table (that
you appended to) and change the primary key to whatever the original table
used as a primary key. In other words, you changed the primary key so that
during the appending you would eliminate duplicates, but now it needs
changing back.

HTH,
CW
 
R

Robbie

I decided to just manually go through the duplicate query, and delete the
ISBN for the duplicate record... since I could sort alphabetically by the
product title, or numerically by the ISBN each item would appear twice in a
row. So I simply clicked in the first field hit delete arrowed twice and
repeated.

Then I went to the products table and deleted where ISBN was null
 
Ad

Advertisements

J

Jeff Boyce

Not saying "can't", offering an option.

I've had more luck (i.e., got done faster) by creating a new table and
filling it with the unique values, then renaming the table.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Robbie said:
So you are saying that I can't delete duplicates so I should rebuild my
table instead?

Jeff Boyce said:
Robbie

One approach might be to first select unique values, then create a new
table that only has those.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Robbie said:
I made a find duplicates query using the wizard, but now I need to delete
the duplicate records. I don't care which one is deleted so long as only
one exists. How can I turn this query into a delete query that will
remove the extra records?

SELECT Products.ISBN, Products.Title, Products.Binding, Products.ISBN13,
Products.ISBN10
FROM Products
WHERE (((Products.ISBN) In (SELECT [ISBN] FROM [Products] As Tmp GROUP
BY [ISBN] HAVING Count(*)>1 )))
ORDER BY Products.ISBN;


Thanks
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top