Re: Delete duplicate rows

"Ioannis Oikonomou" <gecon27_at_hotmail.com> wrote in message news:9e8f78$avh$1_at_ulysses.noc.ntua.gr...
> Hello everybody!
>
> I was testing the following correlated subquery for the table
> duplicateRowsTable (row_id, name):
>
> delete duplicateRowsTable
> where row_id > (
> select min(row_id)
> from duplicateRowsTable m
> where m.name = duplicateRowsTable.name
> )

This looks wrong. Instead I'd try

delete duplicateRowsTable
where row_id NOT IN (
select min(row_id)
from duplicateRowsTable
group by name)

> The contents of the table are:
>
> row_id name
> ------- -----
> 1 A
> 2 B
> 3 C
> 4 A
> 5 B
> 6 C
> 7 A
> 8 B
> 9 C
> 10 A
> 11 B
> 12 C
> 13 D
> 14 E
> 15 D

<Pierre/> Received on Sun Jul 22 2001 - 01:24:31 CEST