Re: Help! Deleting duplicate rows

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 15 Sep 1999 10:20:43 -0400
Message-ID: <6arfN0ZvtEFL7nr84gBMc7y9Ft8b@4ax.com>

A copy of this was sent to sjoshi_at_ingr.com (if that email address didn't require changing) On Wed, 15 Sep 1999 13:58:02 GMT, you wrote:

>Hello All
> Can someone please tell me how to delete duplicate rows from a table
>in Oracle 8.0.4
>

delete from T
where rowid <> ( select max(rowid) from T t2

                   where t2.c1 = t.c1
                     and t2.c2 = t.c2 
                     ..... )

Your predicate in the subquery is used to determine 'what is unique' about the row. You would include every column that is part of the 'unique key' for that row.

>thanks
>Sunit
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 15 1999 - 09:20:43 CDT