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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment