Delete duplicate values

Question

I hava a table emp, I wish to delete the duplicate values. The subquery is good.

SELECT ename 
                 FROM   emp 
                 GROUP  BY ename 
                 HAVING Count(empno) > 1

But when I do the delete operation its deleting all the lines. I don't understand what mistake I'm doing.

DELETE FROM emp 
WHERE  ename IN (SELECT ename 
                 FROM   emp 
                 GROUP  BY ename 
                 HAVING Count(empno) > 1);

Littlefoot · Answer 1 · 2020-02-28T18:50:35.840

3

It deletes all ENAMEs, not only duplicates (i.e. it doesn't leave one row). Try something like

Table contents at beginning:

SQL> select * From temp order by id;

        ID ENAME
---------- ----------
         1 Little
         2 Little          --> duplicate ENAME
         3 Foot

Deleting:

SQL> delete from temp a
  2  where a.rowid > (select min(b.rowid)
  3                   from temp b
  4                   where b.ename = a.ename
  5                  );

1 row deleted.

Result;

SQL> select * From temp order by id;

        ID ENAME
---------- ----------
         1 Little
         3 Foot

SQL>

edited Feb 28 '20 at 18:50

answered Feb 28 '20 at 18:31

Littlefoot

131,892
15
35
57

thanks for you comments but its not deleting any lines – Manoj Rojen Muthuraja Feb 28 '20 at 18:47
Then you're doing something wrong. I edited my answer and added an example which shows that such a principle *works* if you use it properly. – Littlefoot Feb 28 '20 at 18:51

Delete duplicate values

1 Answers1