Do a DELETE query over a SELECT

03 Jun 2006 22:47 - (0) comments

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