Query so slow. was; Re: [AccessD] delete duplicates

Hi Pedro

I'm not sure it your queries work correctly, but if they do they will
run very slow.

You have at least two options.

1. Try this:

    DELETE *
    FROM test
    WHERE PK NOT IN (
      SELECT TOP 1 PK
      FROM test AS T
      WHERE T.FieldA = test.FieldA);

2. Write the FirstPKs of your query1 to a temporary table. Then
include that in query2:

  <query2/>
  DELETE *
  FROM test
  WHERE PK Not In (Select PK from tblTemp);

/gustav


> Hello Group,

> i use the query's below to delete he second or third etc duplicate from
> fieldA.
> It is working fine with 50 records or so. When i use it on a Table with 9000
> records
> query2 still runs after a few hours. What can be wrong?

> TIA

> Pedro Janssen


> <query1/>
> SELECT First(test.PK) AS FirstOfPK, test.FieldA
>>From test
> GROUP BY test.FieldA;


> <query2/>
> DELETE test.FieldA
> FROM test
> WHERE test.PK Not In (Select FirstOfPK from Query1);