Re: Delete Duplicate rows from a table

"SATYA PAL" <spgangwar_at_yahoo.com> wrote in message news:a7b8a58b.0106120820.590b0983_at_posting.google.com...
> Hi There,
>
> I have a frequent requirement to delete the duplicate rows from a
> table. I know one method using the RowID to delete the duplicate from
> the table.
>
> Is there any best mechanism to delete duplicate rows from the table.

Best mechanism I don't know about (other than putting a primary key or unique index on some columns to stop it from happening in the first place) but this should work for cleaning it up.

Here's the test data:

SQLWKS> select * from test;
C C
- -
1 A
1 A
1 X
2 B
2 X
3 C
3 X
4 D
4 X
5 E
5 E
5 X
6 F
6 X
7 G
7 G
7 X
8 H
8 X
9 I
9 I
9 X
22 rows selected.

And the PL/SQL block

DECLARE
    CURSOR cursor1 IS SELECT * FROM test ORDER BY col1, col2 FOR UPDATE;     r_curr test%ROWTYPE;
    r_last test%ROWTYPE;
BEGIN
    OPEN cursor1;
    FETCH cursor1 INTO r_curr;
    WHILE cursor1%FOUND LOOP

        IF ( r_last.col1 = r_curr.col1 AND r_last.col2 = r_curr.col2 ) THEN
            DELETE FROM test WHERE CURRENT OF cursor1;
        END IF;
        r_last := r_curr;
        FETCH cursor1 INTO r_curr;

    You must list all the columns in the order by clause on the cursor declaration, that's the only way you can be sure the duplicates will be together

    You must list each column (twice) in the if statement, I couldn't find a way off hand to compare rows.

Output after running PL/SQL block:
SQLWKS> select * from test;
C C
- -
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
1 X
2 X
3 X
4 X
5 X
6 X
7 X
8 X
9 X
18 rows selected. Received on Tue Jun 12 2001 - 19:46:56 CEST