Re: Question about deleting table duplicates

Russ Bagley wrote:
> "ScottH" <fakeaddress_at_newsgroupsonly.com> wrote in message news:<pOKdnbFXV7ane_SiU-KYvQ@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)

Hmm. This isn't how I saw it.

Pretend data set

rowid col

1     1
2     2
3     1
4     4
5     1
6     6

Ok so the sub query said:
select min(b.rowid)
from table_1 b
where b.col_dup_values = a.col_dup_values

That would return 1, right? The min row where a.col = b.col.

So plug that into the original query:

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)

You get:

delete from table_1 a
where a.rowid > (1)

Or am I missing something?

-- 
Mike Nugent
Programmer/Author/DBA/Admin
In search of employment, email for credentials
news_at_remove-this.illuminatus.org