On Mon, Feb 23, 2009 at 11:40 AM, Hagen Finley <finhagen@stripped> wrote:
> I have a number of duplicate records in my table which are identical hence,
> I can't do a delete on the columns without deleting both records. One
> deletion strategy I have considered is identifying the records by their
> table record id - but I don't know for certain that such an identifier
> exists or how I would show it via a select statement. Any suggestions?
> Hagen Finley
> Boulder, CO
If you do not want to delete duplicates one at a time (i.e. LIMIT 1),
then you need a unique record identifier. MySQL does not have a
default unique record id, you need to create that field yourself. Just
specify it as an auto-increment and you with have a unique ID for each
Check if you already have an auto-increment field, otherwise add one.
To delete your duplicate records, use something similar to the following query.
DELETE tableName FROM tableName JOIN
(SELECT max(recordID) dupID, count(*) c FROM tableName GROUP BY
recordID HAVING c>1) AS dupTable ON tableName.recordID=dupTable.dupID
That will delete the "latest" duplicate record. If you have more than
2 copies of a record (i.e. triplicates), this will not clean the all
out. You can keep running the query to delete multiple duplicates of
Test the query first to make sure it's working properly. Just switch
"DELETE tableNAME" with "SELECT fieldName(s)".