Deleting Duplicate Rows.

[Jose Mon]

IS there any way to eliminate duplicate rows?

Some times my table contains double entries(In all coloumns).
SO is there any way to remove the duplicates?

[Mark Spritzler]

Yes, and it has to do with a where clause to determine a duplicate with a sub-select (I think) and a rowid > rowid. Basically saving the first one it finds and deleting the others.

I just don't remember the exact query. But you can query for it at asktom.oracle.com

Copied from that site

  
delete from t
  where rowid in ( select rid
                     from ( select rowid rid,
                                   row_number() over 
                                          ( partition by x order by rowid ) rn
                              from t
                           )
                    where rn > 1 )
  

that'll remove "dups" based on the column X (just partition by the "key to de-dup by")

If you have an index on that column (and at least one of the columns in the index is NOT NULLABLE),
then it'll use that index in the plan...

Good Luck

Mark

[Jan Cumps]

delete from 
<table>
where rowid not in
(
 select min(rowid)
from
<table>
group by
<all, columns, in, your, table>
)

replace <table> with your table name
replace <all, columns, in, your, table> with all columns in your table.

The subquery will return just one rowid for each unique row (the lowest rowid).
The main query will delete all records that are not in this subquery. They are the duplicates.

carefull:
Take a backup of your table first! Be prepared to rollback! Try it on a test instance first! Disable autocommit!

Regards, Jan

[Srinivasa Raghavan]

Cumps,
I dont want to hijack this thread. Could you Please check your private message I sent. (I need some info on UMP exam).