Re: Duplicate Records

vermaat_at_gemini.tfdl.agro.nl wrote:
>
> In article <326FE2E4.7086_at_indy.net>, Richard William Howell <rwh_at_indy.net> writes:
> >I am trying to write a query which will extract a list of duplicate rows
> >in my table so that I may examine the duplicate records and eliminate
> >the "bad" one or more. Any suggestions appreciated.
>
> INSERT INTO [dump_table]
> SELECT [primary_key_columns concatenated by ||] FROM [table_to_be_checked]
> GROUP BY [primary_key_columns concatenated by ||]
> HAVING COUNT([primary_key_columns concatenated by ||]) > 1;
>
> This should do the trick.
>

Intermediate tables are ok but you might want to try the following:

This example assumes there are two primary key columns ( pk_column_1 and pk_column_2 )

The select statement will give you a list of the duplicate rows. ( Only one of the duplicates )

SELECT columns_of_interest
 FROM table_name a
WHERE rowid > ( SELECT min(rowid)

                  FROM table_name b
                 WHERE b.pk_column_1 = a.pk_column_1
                   and b.pk_column_2 = a.pk_column_2 )

If you want to remove the rows then change the select to a delete ( Only one of the duplicates )

DELETE
  FROM table_name a
 WHERE rowid > ( SELECT min(rowid)

                   FROM table_name b
                  WHERE b.pk_column_1 = a.pk_column_1
                    and b.pk_column_2 = a.pk_column_2 )

NOTE This method will remove only one of the duplicates

PS:
* Don’t forget to add a primary key index !!!!!! * Don’t add a PK constraint unless you really have to.

Regards Gary Piper

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~ Gary Piper ( gpiper_at_ozemail.com.au )
http://www.ozemail.com.au/~gpiper/

Phone  : 61-3-9827-4276
Fax    : 61-3-9827-4917
Mobile : 0419-352-584 ( International 61-419-352-584 )
----------------------------------------------------------------------