Re: DELETE DUPLICATES

In article 3047_at_atlastele.com, brucea_at_atlastele.com (Bruce Arbuckle) writes:
>In article <1va9pm$7mc_at_erau.db.erau.edu> mellott_at_db.erau.edu (David Michael Mellott) writes:
>>does anyone have a simple script to delete duplicates
>>from a table
>>I thank you in advance\mike
>>.
>>
>delete from table
> where rowid in
> (select rowid from table A, table B
> where B.rowid > A.rowid)
>
>Should do the trick.
>
                                                

Actually, Bruce's solution didn't work at all when I tested it.

The rowid in "where rowid.." is ambiguous, and if declared as "a.rowid" it will end up deleting everything but a single record.   

The solution posted by Jacqui Thompson (included below), which I also tested, works fine.

I think it important to test solutions before posting.

In article 740148754_at_cc.und.ac.za, thompson_at_cc.und.ac.za (Jacqui Thompson) writes:
>Hi there,
>
>To delete duplicates from a table, there are
>columns that are duplicated, say col1, col2, col3:-
>
>delete from tab A
>where exists (select 1
> from tab B
> where A.col1 = B.col1 and
> A.col2 = B.col2 and } Add as many columns
> A.col3 = B.col3 and } as make up the dupl.
> A.rowid > B.rowid)
>
>This should get rid of duplicates, triplicates etc. while leaving
>one. For example if you had rows:
> A A B B C A A B (where A is a row etc.)
> the result would be...
> A B C
>

---
___________________________________________________________________________

Jeff.Stander_at_ml.csiro.au        _--_|\        Database Analyst
CSIRO Division Of Fisheries    /      \       Pelagic Fisheries Resources
GPO Box 1538, Hobart           \_.--._/       Tasmania 7001, Australia
Aus Tel: 002-325-332                 v        Intl Tel: +61-02-325-332
Aus Fax: 002-325-000                          Intl Fax: +61-02-325-000
___________________________________________________________________________