List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:February 23 2009 5:04pm
Subject:Re: Record IDs
View as plain text  
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
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
Thread
book categoriesPJ22 Feb
  • Re: book categoriesClaudio Nanni22 Feb
    • Re: book categoriesPJ23 Feb
  • RE: book categoriesJerry Schwartz23 Feb
    • Record IDsHagen Finley23 Feb
      • Re: Record IDsMattia Merzi23 Feb
      • Re: Record IDsBrent Baisley23 Feb
      • RE: Record IDsJerry Schwartz23 Feb