Re: A problem in SQL (a real challenge)

thought <fahd_kamalNOfaSPAM_at_hotmail.com.invalid> wrote in message news:22fe7a4c.ec04d43a_at_usw-ex0105-040.remarq.com...
> Suppose I have a table without a primary key (or for that matter
> any key). I want to delete duplicate entries from the table
> using a SINGLE SQL statement. Can anyone there help me out?

Karl Hewlett suggested a smart solution for this a while ago. This solution utilizes the unique rowid of each row in a table:

DELETE FROM my_table t
WHERE t.rowid = (SELECT max(tt.rowid)

        FROM my_table tt
        WHERE tt.<value-1> = t.<value-1> AND ... tt.value-n = t.value-n)

The general problem would be to remove _all_ duplicate rows for each row in a table:

DELETE FROM my_table t
WHERE EXISTS (

        SELECT *
        FROM my_table tt
        WHERE t.rowid > tt.rowid AND tt.<value-1> = t.<value-1> AND ...

Do note that a table without a primary key violates 1NF, hence should generally be avoided.

--

Thomas