Do a DELETE query over a SELECT

A good tip from my co-workers is to always do a delete over a select. This way it’s easy to check what you are going to delete. If the results are correct you can add the delete.

For example:

SELECT MIN(rowid)
    FROM our_table
    GROUP BY column1, column2, column3...;

If this query returns all records you want to keep you can add the following delete:

DELETE FROM our_table
  WHERE rowid not in
   (SELECT MIN(rowid)
    FROM our_table
    GROUP BY column1, column2, column3...);

admin