Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: identical columns

Re: identical columns

From: Preferred Customer <_at_lineone.net>
Date: Wed, 09 Sep 1998 20:20:04 GMT
Message-ID: <UpBJ1.144$sJ3.449975@news-reader.bt.net>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US