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

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 )

Answer 2

Using rownum to delete duplicate records makes not much sense. If you need to delete duplicate rows, leaving only one row for each value of name, try the following:

DELETE FROM mytable
  WHERE ROWID IN (SELECT ID
                    FROM (SELECT ROWID ID, ROW_NUMBER() OVER 
                             (PARTITION BY name ORDER BY name) numRows FROM mytable
                         )
                   WHERE numRows > 1)

By adding further columns in ORDER BY clause, you can choice to delete the record with greatest/smallest ID, or some other field.