On Mon, Feb 23, 2009 at 11:40 AM, Hagen Finley <finha...@comcast.net> 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 record. 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 records. Test the query first to make sure it's working properly. Just switch "DELETE tableNAME" with "SELECT fieldName(s)". Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
