Hopefully, you've created a primary key constraint on your table. If so, then one or more columns are guaranteed to be unique and you can delete a record just by specifying the primary key columns in the WHERE clause of your DELETE statement. For instance, assume that I have the following table:
ID NAME JOB 1 Bob Janitor 2 Bob JanitorTo delete the duplicate entry, I can use the primary key column, which in this case is the ID column.
DELETE FROM table WHERE id=2;
So what do you do if you do not have a primary key constraint? Well the first thing you do is slap yourself on the hand because you've created a table without one. All tables should have a PK constraint. Assume the table looks like the following:
NAME JOB Bob Janitor Bob JanitorThere is no PK column and you want to remove one and only one row. Then a query similar to the following can help:
DELETE FROM table WHERE rowid IN ( SELECT MIN(rowid) FROM table GROUP BY name,job HAVING COUNT(*) > 1);
You can also search AskTom for more examples.
Once you have cleaned up your duplicate data, the next task is to create a PK constraint on your table so that this does not happen again. This is one of the things the PK constraint was designed for.
This was first published in April 2006