Oracle: Why I cannot rely on ROWNUM in a delete clause

Question

I have a such statement:

SELECT MIN(ROWNUM) FROM my_table
GROUP BY NAME 
HAVING COUNT(NAME) > 1);

This statement gives me the rownum of the first duplicate, but when transform this statement into DELETE it just delete everything. Why does it happen so?

Answer 1

This is because ROWNUM is a pseudo column which implies that they do not exist physically. You can better use rowid to delete the records.

To remove the duplicates you can try like this:

DELETE FROM mytable a
WHERE EXISTS( SELECT 1 FROM mytable b
              WHERE a.id = b.id
              AND a.name = b.name
              AND a.rowid > b.rowid )