Re: Question about deleting table duplicates

"ScottH" <fakeaddress_at_newsgroupsonly.com> wrote in message news:<pOKdnbFXV7ane_SiU-KYvQ_at_giganews.com>...
> I was looking for thw SQL to delete dupes from a table, and came across
> this. All who saw it agreed in principle, but I can't quite figure out the
> logic. If we are deleting all rows whose rowid is greater than the least of
> the rowids returned from creating the subset of dupes, couldn't we
> inadvertently delete some non-dupes rows that were created after the last
> dupe ? I mean, any row created after the last dupe would have a greater
> rowid, wouldn't it ?
>
> Here's the SQL:
>
> delete from table_1 a
> where a.rowid >
> (select min(b.rowid)
> from table_1 b
> where b.col_dup_values = a.col_dup_values)
>
> By the way, should the delete ALL dupes, including the originals ? That is
> all rows participating in duplicity, as it is, will be gone.
>
> Thanks,
> Scott

No, you will delete rows which are identical, save for their rowids. Rowids aren't stored, they are an internal attribute of a record. So if you have 2 identical rows, only 1 will be deleted. A *random* one of course, because rows are inserted randomly. If you would limit the delete with an extra where clause to one particular case, you should see easily that only one record is deleted.
If more rows are deleted, the where clause in the subquery is incorrect.

Sybrand Bakker
Senior Oracle DBA Received on Fri Sep 19 2003 - 12:58:55 CEST