However, this is inefficient as it can be done without shifting around a lot of data.

To comply with these requirements, all rows are typically loaded into the application's memory in small chunks and some programming code calculates the duplicate rows to delete. In some cases, additional columns set a priority to keep a row:Ī verified user should not be deleted to keep an unverified one. The remaining row should not be the first or last one created. Instead of deleting all duplicate rows, some should be kept.ĭuplicate rows may be a valid use-case for some applications but they should be limited to e.g.

This simple way of deleting duplicate rows will not work when additional requirements have to be followed: On the duplicate columns and keep one remaining row using the MIN (id) While inserting them was easy, removing them is a much more complex task. However, the user experience is affected quickly with the duplicate rows listed repeatedly, and the database performance will degrade because of the larger data size.įurthermore, an efficient unique index to tell the database it can stop searching after the first row has been found cannot be used anymore. No matter how good an application is, there may be duplicate rows after some time. WITH duplicates AS ( SELECT id, ROW_NUMBER () OVER ( PARTITION BY firstname, lastname, email ORDER BY age DESC ) AS rownum FROM contacts ) DELETE FROM contacts USING duplicates WHERE contacts.id = duplicates.id AND duplicates.rownum > 1 Detailed Explanation

Delete duplicate rows in sql