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

               (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

How to Pass Parameters to ActionListener in ADF

In some cases, it is required to pass a value to ActionListener of ADF Button. The method that can be invoked by actionListeners has only...