Delete Duplicate Records

[novice]

Hi,

I'm trying to write a query to delete the duplicate records in a Table
The table (Table1) contains a single column (TestInput) with many records.

I would like to delete the duplicate records from this table leaving atmost one of the duplicate.

I tried to write the query as:
delete T1 from Table1 T1, Table1 T2 where T1.TestInput = T2.TestInput;

But this doesnot work. Can any one please let me know on how to proceed with this query. Thanks.

 

[ajetrumpet]

novice,

i don't think there is a way you can do this with a query itself unless you reference another table that has the same records in it, and also an identifier like an autonumber that tells you what record you are dealing with.

you can loop the recordset as a last resort and compare the records to the one you are on, but that would require a function to be written.

 

[neileg]

Proably the easiest way is to do a SELECT DISTINCT query and this will pull your unique values. You can either store ththe results of the query in another table and delete the old one, or just use the query. Depends on the structure of your database and whether this is a one off exercise.