Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: identical columns
Corinna
If duplicate rows is defined as, for example,
colA and colB are identical
then you could find the duplicate rows by
SELECT colA,colB
FROM tablename
GROUP BY colA,colB
HAVING COUNT(*) > 1;
If you want to information about other columns on the duplicate row,
eg. colC, then join the table to itself
SELECT colC
FROM tablename t1
WHERE t1.colA||t1.colB
IN
SELECT colA||colB
FROM tablename t2
GROUP BY colA||colB
HAVING COUNT(*) > 1);
and finally if you want to get rid of all the duplicate rows and leave only
a unique set, then
DELETE tablename
WHERE rowid IN
(SELECT t1.rowid
FROM tablename t1,
tablename t2
WHERE t1.colA = t2.colA
AND t1.colB = t2.colB AND t1.rowid > t2.rowid);
For a large table this statement will be much faster if you build an index on the columns which define duplication, colA and colB in this example
Rupert
Corinna Becker wrote in message
<01bddbfd$5f3ec540$a049100a_at_pc743-sode.seurope.ikea.com>...
>Hello,
>I have the following problem: I have a table of which I know that it
>contains duplicate rows. I want to find all rows that are duplicate.
>How can I do that?
>Regards and thanks
>Corinna Becker
Received on Wed Sep 09 1998 - 15:20:04 CDT