Let’s see how we can delete the duplicate rows in a table. First we need to find the duplicated records by comparing/grouping columns, then we’ll use ROWID to separate the rows.
Here’s a script to generate the sample table:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE sampletable ( no NUMBER, name VARCHAR2(30), phone VARCHAR2(11)); INSERT INTO sampletable VALUES( 1, 'GOKHAN', '232-4446735' ); INSERT INTO sampletable VALUES( 2, 'GOKHAN', '232-4446735' ); INSERT INTO sampletable VALUES( 3, 'GOKHAN', '232-4446735' ); INSERT INTO sampletable VALUES( 4, 'TOM', '800-4446735' ); INSERT INTO sampletable VALUES( 5, 'TOM', '800-4446735' ); INSERT INTO sampletable VALUES( 6, 'TOM', '800-4446735' ); INSERT INTO sampletable VALUES( 7, 'MIKE', '900-4446735' ); INSERT INTO sampletable VALUES( 8, 'MIKE', '900-4446735' ); COMMIT; |
In this table, column named “no” is unique, but I’ll use ROWID to identify the rows. I’ve added this column to show which records will not be deleted.
Let’s remove the duplicate rows:
|
1 2 3 4 5 |
DELETE FROM SAMPLETABLE S WHERE S.ROWID = (SELECT MIN(ROWID) FROM SAMPLETABLE ST WHERE S.NAME = ST.NAME AND S.PHONE = ST.PHONE); |
Here’s the result after deleting rows:
NO NAME PHONE
——————————
1 GOKHAN 232-4446735
4 TOM 800-4446735
7 MIKE 900-4446735
