List:General Discussion« Previous MessageNext Message »
From:David Lerer Date:March 30 2014 3:18am
Subject:RE: Help with cleaning up data
View as plain text  
Bill, here is one approach:

The following query will return the id's that should NOT be deleted:
  Select min (id) from icd9x10 group by icd9, icd10

Once you run it and happy with the results then you subquery it in a DELETE statement. Something like:
   Delete from icd9x10 A where A.id not in (Select min (B.id) from icd9x10 B group by B.icd9, B.icd10).

I have not tested it (sorry it is a weekend here...), but I hope it will lead you into the right direction.

David.


David Lerer | Director, Database Administration | Interactive | 605 Third Avenue, 12th Floor, New York, NY 10158
Direct: (646) 487-6522 | Fax: (646) 487-1569 | dlerer@stripped | www.univision.net

-----Original Message-----
From: william drescher [mailto:william@stripped]
Sent: Saturday, March 29, 2014 2:26 PM
To: mysql@stripped
Subject: Help with cleaning up data

I am given a table: ICD9X10 which is a maping of ICD9 codes to
ICD10 codes.  Unfortunately the table contains duplicate entries
that I need to remove.

CREATE TABLE `ICD9X10` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `icd9` char(8) NOT NULL,
  `icd10` char(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `icd9` (`icd9`,`id`),
  UNIQUE KEY `icd10` (`icd10`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=671 DEFAULT CHARSET=ascii

id   icd9  icd10
25   29182 F10182
26   29182 F10282
27   29182 F10982

I just can't think of a way to write a querey to delete the
duplicates.  Does anyone have a suggestion ?

bill


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql

The information contained in this e-mail and any attached 

documents may be privileged, confidential and protected from 

disclosure. If you are not the intended recipient you may not 

read, copy, distribute or use this information. If you have 

received this communication in error, please notify the sender 

immediately by replying to this message and then delete it 

from your system.
Thread
Help with cleaning up datawilliam drescher29 Mar 2014
  • Re: Help with cleaning up dataFran Garcia29 Mar 2014
  • Re: Help with cleaning up dataCarsten Pedersen29 Mar 2014
  • RE: Help with cleaning up dataDavid Lerer30 Mar 2014
  • Re: Help with cleaning up datawilliam drescher30 Mar 2014
Re: Help with cleaning up dataBob Eby31 Mar 2014