Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: How to find Duplicate records
In article <2a89f9.0107232026.481337f_at_posting.google.com>, "Shahid
Mahmood" <Shahid.Mahmood_at_team.telstra.com> wrote:
> Hi
> I am trying to find out the duplicate records in the table which
> contains over 10 million records. Could you please let me know the
> easiest and quickest way to get it done.
Try doing a self join, with a <> on the column that could be causing the duplication. For example, assuming something like customer id and location code are columns in the table customer:
SELECT c1.customerid, c1.locationcode
FROM customer c1, customer c2
WHERE c1.customerid = c2.customerid
AND c1.locationcode <> c2.locationcode;
This will give you all customer id's that have more than one location associated with them. Each one will show up twice, so you'll probably want to add some more specific where clauses to filter it further.
-- Latka; [x-post removed]Received on Mon Jul 23 2001 - 23:45:18 CDT