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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.