Q

Deleting a duplicate record from a table

How to delete a duplicate record from the table?

How to delete a duplicate record from the table?

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close