Re: how to delete duplicate tuples???/

lalit wrote:
> hey guys ,,
> i m new to oracle and i m having a problem...
> how can i find out duplicate tuples and then delete them....
> plz reply.

GROUP BY isn't any different in Oracle than in any other DBMS:

select keycol1, keycol2, keycol3
from mytable
group by keycol1, keycol2, keycol3
having count(*) > 1;

will return all duplicate keys, presuming, of course, your primary key is composed of three columns. Of course, since you don't have a primary key defined you have duplicates. So you first need to decide what you will use as your primary key. Then you need to find the duplicates with a query similar to that which I posted above. After THAT you'll need to use the query which returned your duplicate keys to actually return ROWIDs of the duplicated rows. Then you need to decide whether you want to keep the lowest or highest ROWID value in each group, and delete accordingly. As an example:

delete from mytable
where rowid in (select max(rowid) from mytable group by keycol1, keycol2, keycol3 having count(*) > 1);

Such a statement would delete the rows with the largest ROWID values in the table for each key grouping. If you have more than 2 rows with the same key the statement would need to be executed until the output states:

0 rows deleted.

You would then have nothing but unique keys in your table.

David Fitzjarrell Received on Wed Dec 13 2006 - 12:06:58 CST