> 
> Hello Ari
> I would appreciate if I could get sql utility to delete duplicate rows
> from a table
> Thanks in advance 
> Suresh
> Sollala@Informatica.com  
> 
You will have to create a new SQL statement for each table, since the SQL will
depend on the structure of the table. Follow the example shown below:

Assume there is a table TABLE_A with column_a, column_b, and column_c.
I will assume that a "duplicate row" has the same data in all three columns.

To determine this, try:

SELECT column_a, column_b, column_c, count(*)
FROM table_a
HAVING count(*) > 1
GROUP BY column_a, column_b, column_c
/

This will show which columns are duplicated. You can delete ALL duplicates with
the following:

delete from table_name A where A.rowid >
   (select min(B.rowid) from table_name B where A.col = B.col);

or you can also try:

DELETE FROM x
WHERE rowid NOT IN
    (SELECT max(rowid) FROM x
     GROUP BY fieldname1,fieldname2,fieldname3..
     HAVING count(*)>=1)

You can delete just one of the duplicate sets with the following:

DELETE FROM table_a
WHERE column_a = 'XXX' AND column_b = 'YYY' and column_c = 'ZZZ'
AND rownum < 2
/

This will delete just the FIRST record with the duplicate data. You will have to
iteratively do this for all such data. There is probably a more elegant way
to do this but I can't think of any right now.

Best of luck,


-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 175+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page F="../index.htm"> Back to Ari Kaplan's Home Page