Deleting a duplicate record from a table

How to delete a duplicate record from the table?

    Requires Free Membership to View

    Login

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   Janitor
To 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   Janitor
There 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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top