Oracle : How to delete duplicates rows when no distinct value exists?

Question

We have a table with many rows duplicated (2 to 4 times) (each row has the same value for each column). This table has 4 columns but no primary key.

So how to delete the duplicated rows ?

Instance  status  updatetime              name
-----------------------------------------------
gdt546    4       2016/02/13 10:13:18     basic
gdt546    4       2016/02/13 10:13:18     basic
ort87a    1       2016/02/16 19:09:43     High
ort87a    1       2016/02/16 19:09:43     High
ort87a    1       2016/02/16 19:09:43     High

Edited:

The output should be:

 Instance  status  updatetime              name
-----------------------------------------------
gdt546    4       2016/02/13 10:13:18     basic
ort87a    1       2016/02/16 19:09:43     High

Possible duplicate of [Remove duplicate rows in MySQL](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Emmett, Feb 20 '16 at 09:20
visit [ Remove duplicate rows in MySQL](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — user3226739, Feb 20 '16 at 09:22
@Emmett the solution from this link won't work on Oracle, because Oracle dosesn't support `ALTER IGNORE TABLE ...`. This works only on MySql — krokodilko, Feb 20 '16 at 09:23
Can you try the following `DELETE FROM table_name A WHERE a.rowid > ANY (SELECT B.rowid FROM table_name B WHERE A.Instance=B.Instance AND A.status=B.status AND A.updatetime=B.updatetime AND A.name=B.name);` — Madhusudana Reddy Sunnapu, Feb 20 '16 at 09:29
@kkung In the above please replace `a.rowid` with `A.rowid` while you test. — Madhusudana Reddy Sunnapu, Feb 20 '16 at 09:49
@kkung, is the intent to remove the duplicate rows and retain only one of them or remove them all? I mean in your example should there be 0 rows or 2 after duplicate deletion? — Madhusudana Reddy Sunnapu, Feb 20 '16 at 10:08
@MadhusudanaReddySunnapu: you should add that as an answer (btw: there is no difference between `a.rowid` and `A.rowid`) — , Feb 20 '16 at 10:43
@a_horse_with_no_name thanks for that case-insensitive related comment. Added it as an answer stating my assumption. — Madhusudana Reddy Sunnapu, Feb 20 '16 at 10:55
@kkung could you try my query in the answer below and see if that helps. — Madhusudana Reddy Sunnapu, Feb 20 '16 at 11:52

Madhusudana Reddy Sunnapu · Accepted Answer · 2016-02-20T13:41:43.483

4

Assuming the name of the table is TABLE_NAME and the intent is to delete the duplicate rows such that it retains only one of them, please find the below query:

DELETE FROM table_name A 
WHERE  A.ROWID > ANY (SELECT B.ROWID 
                      FROM   table_name B 
                      WHERE  A.INSTANCE = B.INSTANCE 
                             AND A.status = B.status 
                             AND A.updatetime = B.updatetime 
                             AND A.name = B.name);

edited Feb 20 '16 at 13:41

answered Feb 20 '16 at 10:54

Madhusudana Reddy Sunnapu

8,064
2
18
29

@GordonLinoff Agree and thanks for the suggestion. Now formatted. – Madhusudana Reddy Sunnapu Feb 20 '16 at 13:44

score 0 · Answer 2 · answered Feb 20 '16 at 11:02

Here you have a solution based on analytic function row_number(). This is the table you want to dedup:

SQL> select * from test1;
INSTANCE|STATUS|UPDATETIME         |NAME    
--------+------+-------------------+--------
gdt546  |     4|2016-02-13 10:13:18|basic   
gdt546  |     4|2016-02-13 10:13:18|basic   
ort87a  |     1|2016-02-16 19:09:43|High    
ort87a  |     1|2016-02-16 19:09:43|High    
ort87a  |     1|2016-02-16 19:09:43|High

And here you have de-duped rows:

SQL> select instance, status, updatetime, name from 
     ( select row_number() over (partition by instance, status, 
       updatetime, name order by status) as rn, test1.* from test1 ) a
     where a.rn = 1 ;
INSTANCE|STATUS|UPDATETIME         |NAME    
--------+------+-------------------+--------
gdt546  |     4|2016-02-13 10:13:18|basic   
ort87a  |     1|2016-02-16 19:09:43|High

Oracle : How to delete duplicates rows when no distinct value exists?

2 Answers2