How to delete duplicate rows in oracle database

I want to show you example of how to delete duplicate rows from a table.
create some table:

CREATE TABLE footballers (Id NUMBER, FirstName VARCHAR2(30), LastName VARCHAR2(30));

add some duplicate rows to it:

INSERT INTO footballers VALUES (1, 'Cristiano', 'Ronaldo');
INSERT INTO footballers VALUES (2, 'Lionel', 'Messi');
INSERT INTO footballers VALUES (1, 'Cristiano', 'Ronaldo');
INSERT INTO footballers VALUES (2, 'Lionel', 'Messi');
INSERT INTO footballers VALUES (1, 'Cristiano', 'Ronaldo');
INSERT INTO footballers VALUES (2, 'Lionel', 'Messi');
commit;

check data in our table:

SELECT * FROM footballers;
1  Cristiano  Ronaldo
2  Lionel     Messi
1  Cristiano  Ronaldo
2  Lionel     Messi
1  Cristiano  Ronaldo
2  Lionel     Messi

Now let’s delete duplicate rows using this script:

 
DELETE FROM footballers   
WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM footballers GROUP BY ID);
commit;

check data in our footballers table:

SELECT * FROM footballers
1  Cristiano  Ronaldo
2  Lionel     Messi

One thought on “How to delete duplicate rows in oracle database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s