Re: Need to delete duplicate rows

Thomas Muller wrote:
>
> Karl Hewlett <fake_address_at_auckland.ac.nz> wrote in message
> news:8dgiks$jk2$1_at_scream.auckland.ac.nz...
> > Someone suggested select distinct into a temp table which will work,
 another
> > solution would be:
> >
> > (Its been a while since I have had to do this so you may have to play with
> > the syntax)
> >
> > delete from sales a
> > where rowid = (select max(b.rowid)
> > where a.item_code = b.item_code
> > and <snip>
> > );
> >
> > Naturally run the equivilent select count(*) first to check how many you
> > will delete. :)
> >
>
> Very nice and clever!
>
> --
>
> Thomas

This is fine for Oracle, but rowid is not valid for other DBMS. Some have an equivalent construct, but the SQL standard most definitely does not support the notion of a row identifier. In fact, it is anathema to relational theory all together. In a properly specified relation/table - one that has a proper primary key - duplicates tuples/rows are not possible as a relation is properly a set.

William Boyle Received on Wed Jun 21 2000 - 00:00:00 CEST