Saturday, April 17, 2010

Different ways to delete duplicate rows in a table

CREATE TABLE duplicate_test(name VAR CHAR2(20), age NUMBER);

begin
INSERT INTO duplicate_test VALUES('srik',20);
INSERT INTO duplicate_test VALUES('srik',20);
INSERT INTO duplicate_test VALUES('sr',22);
INSERT INTO duplicate_test VALUES('sr',22);
INSERT INTO duplicate_test VALUES('ik',24);
INSERT INTO duplicate_test VALUES('ik',24)
end;
/

way1:

DECLARE
CURSOR c1
IS
SELECT DISTINCT *
FROM duplicate_test;
r1 c1%ROWTYPE;
BEGIN
OPEN c1;
DELETE FROM duplicate_test;
FETCH c1
INTO r1;
WHILE c1%FOUND
LOOP
INSERT INTO duplicate_test
VALUES r1;
FETCH c1
INTO r1;
END LOOP;
COMMIT;
END;
/

Way2:
CREATE TABLE duplicate_test(age NUMBER);

INSERT INTO duplicate_test VALUES(20)
INSERT INTO duplicate_test VALUES(20)
INSERT INTO duplicate_test VALUES(22)
INSERT INTO duplicate_test VALUES(22)

CREATE OR REPLACE TYPE ntable IS TABLE OF NUMBER;

DECLARE
t1 ntable;
BEGIN
SELECT age BULK COLLECT INTO t1 FROM duplicate_test;
t1 := SET(t1);
DELETE FROM duplicate_test;
INSERT INTO duplicate_Test SELECT * FROM TABLE(t1);
END;

Way 3:
Using the flashback query

Way 4:
Using the distinct key word

Way 5:
Using the rowid

No comments: