20 February, 2012

Delete Duplicate Rows in Oracle Database


I have a table CMS_TRANS contains a lot of duplicate row.
I should delete duplicated rows from this table.

The structure of table as below

create or replace table CMS_TRANS(
trans_id number ,
trans_code varchar2(10),
trans_date date,
trans_desc varchar2(500) );

This table doesn't has primary key so redundant data is allowed, so I want to delete duplicate data which are repeated in trans_code,trans_date columns

I saw a lot of  solution of other people to do this, but I was surprising as there is easy solution that I will write it and they use complex solution to do this.

My solution is

DELETE FROM CMS_TRANS
      WHERE ROWID IN
               (SELECT ROWID
                  FROM CMS_TRANS T1
                 WHERE EXISTS
                          (SELECT 1
                             FROM CMS_TRANS T2
                            WHERE     T1.TRANS_CODE = T2.TRANS_CODE
                                  AND T1.TRANS_DATE = T2.TRANS_DATE
                                  AND T1.ROWID > T2.ROWID))



Mahmoud Ahmed El-Sayed

No comments:

Post a Comment

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...