List:General Discussion« Previous MessageNext Message »
From:Fran Garcia Date:March 29 2014 7:15pm
Subject:Re: Help with cleaning up data
View as plain text  
Hi Bill,

How big is your table? It seems to me that you might want to change your
unique keys to something like (icd9, icd10), thus guaranteeing that every
mapping will exist only once in your table. You could create a new table
with that constraint and copy all your data to it:

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

INSERT IGNORE INTO ICD9X10_2 SELECT * FROM ICD9X10; -- This will skip the
duplicates

-- Once you've checked the new table and it looks fine to you, you can swap
them:
RENAME TABLE ICD9X10 TO ICD9X10_old, ICD9X10_2 TO ICD9X10;


Or, alternatively, you can also directly alter your table by adding that
unique index like this:
ALTER IGNORE TABLE ICD9X10 ADD UNIQUE KEY (ICD9, ICD10);

Hope that helps



2014-03-29 18:26 GMT+00:00 william drescher <william@stripped>:

> 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
>
>

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