Re: Deleting duplicate rows in a table in oracle

maheshc25 <member24992_at_dbforums.com> wrote in message news:<2530874.1045247171_at_dbforums.com>...
> Hi gurus,
>
>
> i want to know the single query which deletes the duplicate rows
> of a table.
>

SQL> create table foo (t1 number);

Table created.

SQL> insert into foo values (1);

1 row created.

SQL> insert into foo values (2);

1 row created.

SQL> insert into foo values (3);

1 row created.

SQL> insert into foo values (4);

1 row created.

SQL> insert into foo values (1);

1 row created.

SQL> insert into foo values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo;

        T1

         1
         2
         3
         4
         1
         3

SQL> DELETE FROM foo 
  2     WHERE rowid IN
  3              (SELECT rowid 
  4               FROM foo 
  5               MINUS
  6               SELECT MIN(rowid) 
  7               FROM foo 
  8               GROUP BY t1);

2 rows deleted.

SQL> select * from foo;

        T1

         1
         2
         3
         4

Regards
/Rauf Sarwar Received on Sat Feb 15 2003 - 00:33:03 CET