How to delete duplicate rows when table does not have primary key

Deleting rows from a table with primary key is a straight forward approach. Primary key column is all we need. What about a table that does not have primary key? This is where we need to use table's ROWID.

Conside the below situation.

SQL> SELECT * FROM emp;
         A        B
---------- --------
        10 prajeeth
        11 prajeeth
        12 Kayakkal
        13 Kayakkal
        14 lasdfj;a
        15 hello
         5 hello
        10 prajeeth
        11 prajeeth
        12 Kayakkal
        13 Kayakkal

11 rows selected.

We see that this table has lots of duplicate records.

Now select the table along with ROWID

	
SQL> SELECT ROWID, a, b FROM emp ORDER BY a;

ROWID                                  A        B
-------------------------------- ------ ------------
AAADevAAEAAAAC2AAA          5 hello
AAADevAAEAAAACzAAA         10 prajeeth
AAADevAAEAAAAC2AAB         10 prajeeth
AAADevAAEAAAACzAAB         11 prajeeth
AAADevAAEAAAAC2AAC         11 prajeeth
AAADevAAEAAAACzAAC         12 Kayakkal
AAADevAAEAAAAC2AAD         12 Kayakkal
AAADevAAEAAAACzAAD         13 Kayakkal
AAADevAAEAAAAC2AAE         13 Kayakkal
AAADevAAEAAAAC0AAA         14 lasdfj;a
AAADevAAEAAAAC0AAB         15 hello

11 rows selected.
SQL> SELECT a FROM emp GROUP BY a HAVING count (a) > 1 ORDER BY a;

         A
----------
        10
        11
        12
        13

  	
SQL> SELECT max (rowid), a FROM emp GROUP BY a HAVING count (a) > 1 ORDER BY a;

MAX(ROWID)                          A
----------------------------------- -----------
AAADevAAEAAAAC2AAB         10
AAADevAAEAAAAC2AAC         11
AAADevAAEAAAAC2AAD         12
AAADevAAEAAAAC2AAE         13
  	
SQL> DELETE FROM emp WHERE rowid IN (SELECT max (rowid) FROM emp 
2  GROUP BY a HAVING count (a) > 1);

4 rows deleted.
		
	

Note : Do not use ORDER BY clause while using SELECT in the subquery. You will get the below error.

SQL> DELETE FROM emp WHERE rowid IN (SELECT max (rowid) FROM emp 
2   GROUP BY a HAVING count (a) > 1 ORDER BY a);
DELETE FROM emp WHERE rowid IN (SELECT max (rowid) FROM emp 
GROUP BY a HAVING count (a) > 1 ORDER BY a)
                                *
ERROR at line 1:
ORA-00907: missing right parenthesis
  	
SQL> SELECT a, b FROM emp ORDER BY a;

         A B
---------- --------
         5 hello
        10 prajeeth
        11 prajeeth
        12 Kayakkal
        13 Kayakkal
        14 lasdfj;a
        15 hello

7 rows selected.

  	

Do not forget to commit;

SQL> COMMIT;
Comments