Ask the Expert

Deleting a duplicate record from a table

How to delete a duplicate record from the table?

    Requires Free Membership to View

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:

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.

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:

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: