Deleting Duplicates

Hey all.

I have a table with 100,000 plus records in it, and some are duplicates. Is there any way to delete one of them and not the other. For instance, if I duplicate the table I could run this query.
<cfquery name="query1" datasource="datasource">
DELETE DISTINCT
FROM tablename
WHERE FirstName in ( SELECT FirstName from tablename1 where tablename1.FirstName = tablename.FIRST_NAME AND tablename1.LastName = tablename.LAST_NAME AND tablename1.State = tablename.STATE)
</cfquery>

However, it doesn't work. I know the distinct is not correct. But does anyone know how to achieve this, I have looked all over, and everything I try deletes both records. I was thinking of using some kindof count statement, but it still deletes both of them. Please help. Thanks

Just a note that this is really a SQL question and not a CF question.

You can do this but it can be complicated depending on how your table and records are set up and whether or not there is a unique identifier for each record. Do a Google search for "sql delete duplicates". The real lession here is that if you don't want duplicate records in your database, you should have a unique key defined that prevents more than one record with the same values from being added in the first place.

go to:
http://builder.com.com/5100-31-5074183.html#CodeExample1


This will sort you out - fantastic bit of information

Stuart

stuartrobb, please provide the correct url, that one refers to building snowflakes in javascript

otherwise devshed will simply delete your post and consider banning you

Here is the SQL syntax to find dups:

select a.* from table1 a
where 1 < (select count(b.*) from table2 b
where a.column1 = b.column1
and a.column2 = b.column2)