Oracle: Why I Cannot Rely On Rownum In A Delete Clause

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

Solution 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:

DELETEFROM mytable a
WHEREEXISTS( SELECT1FROM mytable b
              WHERE a.id = b.id
              AND a.name = b.name
              AND a.rowid > b.rowid )

Solution 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:

DELETEFROM mytable
  WHERE ROWID IN (SELECT ID
                    FROM (SELECT ROWID ID, ROW_NUMBER() OVER 
                             (PARTITIONBY name ORDERBY 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.