Snippet Name: Eliminate duplicate records Description: Simply runs through the table and eliminates duplicate records. Very fast. Comment: (none) Language: PL/SQL Highlight Mode: PLSQL Last Modified: February 27th, 2009
Description: Simply runs through the table and eliminates duplicate records. Very fast.
CREATE OR REPLACE PROCEDURE dupes AS CURSOR dupes_cur IS SELECT task_code, ROWID FROM jwc_task_code_test ORDER BY task_code; dupes_rec dupes_cur%ROWTYPE; prev_val VARCHAR2(100); cur_val VARCHAR2(100); BEGIN OPEN dupes_cur; prev_val := ''; LOOP FETCH dupes_cur INTO dupes_rec; EXIT WHEN dupes_cur%notfound; IF prev_val = dupes_rec.task_code THEN DELETE FROM jwc_task_code_test WHERE ROWID = dupes_rec.ROWID; ELSE NULL; END IF; prev_val := dupes_rec.task_code; END LOOP; CLOSE dupes_cur; END dupes; -------------------------------------------------- // Another method, courtesy Mariusz Gesicki DELETE TABLE_WITH_DUPS twd WHERE twd.ID IN (SELECT t2.ID FROM (SELECT ROW_NUMBER() OVER (PARTITION BY t1.TASK_CODE ORDER BY t1.ID NULLS LAST) AS DUP_NUMBER, t1.ID FROM TABLE_WITH_DUPS t1 ORDER BY t1.TASK_CODE) t2 WHERE t2.DUP_NUMBER > 1);