[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Originally posted by Jeanne Boyarsky:
Corey,
The easiest way I can think of is to copy all the records to a temp table and then re-insert them using select distinct.
While I can imagine a way of identifying duplicates (using group by and having), I can't think of a way to delete them in a non database specific way. Of course, you probably don't care about that as you are dealing with a specific database. Which one?
I'm curious if anyone has a reply that doesn't involve a temp table.
Originally posted by Paul Campbell:
delete from mytable
where myPK not in ( select min(myPK)
from mytable
group by SomeID )
http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Originally posted by steve souza:
Just wanted to make a further point for anyone that may read the post. It is usually easy enough to write a query to delete the duplicates, however in most cases you don't simply want to delete a record based on an aggregate function.
It often can require analysis to know which of the dupes should be removed. For example you may have the same product several times with all different prices. The one to keep is the one with the right price. That often takes analysis. God help you if you have many dupes in a large table.
That is why it is so important to not allow duplicates in the first place.
Originally posted by Paul Campbell:
you're welcome... it is one of those solutions that a lot of very, very smart people find hard to visualize when they first encounter it since it requires you to think counter from how most of us solve problems.
[OCP 17 book] | [OCP 11 book] | [OCA 8 book] [OCP 8 book] [Practice tests book] [Blog] [JavaRanch FAQ] [How To Ask Questions] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Consider Paul's rocket mass heater. |