Google it ....

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

1 comment: