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?
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 ) 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.
