How delete duplicate rows
Posted by Kamran Agayev A. on December 6th, 2008
Today I want to give you an example of how delete duplicate rows from a table
Let’s create new table with name students:
CREATE TABLE students (ID NUMBER, NAME VARCHAR2(10), surname VARCHAR2(10))
Now let’s add some duplicate rows to it:
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
INSERT INTO students VALUES (1, ‘Kamran’, ‘Agayev’);
INSERT INTO students VALUES (2, ‘Elcin’, ‘Mammedov’);
And now let’s look to the data of our table:
SELECT * FROM students
1 Kamran Agayev
2 Elcin Mammedov
1 Kamran Agayev
2 Elcin Mammedov
1 Kamran Agayev
2 Elcin Mammedov
Now let’s delete duplicate rows using this script:
DELETE FROM students
WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM students GROUP BY ID)
And now let’s look to students table
SELECT * FROM students
1 Kamran Agayev
2 Elcin Mammedov
December 9th, 2008 at 11:49 am
when data is huge performance can be a primary concern, so check asktom on Removing Duplicates – http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html
December 9th, 2008 at 11:58 am
Thank you very much Tonguc for directing us to Tom’s valuable answer. It’s much more speeder by using analytic function.
June 14th, 2009 at 2:06 am
Did you actually test this anywhere ?
June 17th, 2009 at 9:35 am
Yes of course! Don’t you think it should work?
September 1st, 2009 at 11:18 am
thank you its works
September 1st, 2009 at 11:21 am
You’re welcome Ravi
June 12th, 2012 at 6:11 am
hi Kamran,
mashaALLAH…you blog is really informatics and your work is also really superb.
Dear, I am getting issue regarding duplicates records, please find below my thread against find and delete duplicate records, in my case there are number of duplicate records and i m facing issue to find and delete all dup. records,
http://scn.sap.com/thread/2129829
Regards,
ma jamil
June 15th, 2012 at 7:41 am
Have you checked the script I’ve provided in this article? Have you run it? What you got?