Google it ....

Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Wednesday, January 29, 2014

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