在表中插入多行而不插入重复行

I would like to merge a table that the data comes from 2 differents databases. I operated as described below:

我想合并一个数据来自2个不同数据库的表。我操作如下:

1 – I have done a dump of the source database table and I get the following insert query:

1 - 我已完成源数据库表的转储,并获得以下插入查询:

INSERT INTO `t_vaccination` VALUES     (242,NULL,NULL,53,1,'20030528','0','W5770-2',0,'DTP - REVAXIS','A 20130521170623','2013-05-21 17:06:23'),
(243,NULL,NULL,53,1,'20130525','0','',1,'DTP - ','A 20130521170623','2013-05-21 17:06:23'),
(1830,NULL,NULL,50,1,'20080502','3','',0,'DTP - REVAXIS','A 20130521170623','2013-05-21 17:06:23'),
(1831,NULL,NULL,50,1,'20130501','4','',1,'DTP - ','A 20130521170623','2013-05-21 17:06:23'),
(1832,NULL,NULL,50,1,'20080502','3','',0,'PAPILLOMAVIRUS - Gardasil','A 20130521170623','2013-05-21 17:06:23')

the structure of the t_vaccination table is:

t_vaccination表的结构是:

CREATE TABLE `t_vaccination` (
`nIdVaccination` INT(10) UNSIGNED NOT NULL,
`nIdVaccin` INT(10) UNSIGNED NULL DEFAULT NULL,
`nIdVacProtocole` INT(10) UNSIGNED NULL DEFAULT NULL,
`nIdPatient` INT(10) UNSIGNED NOT NULL,
`nIdUtilisateur` INT(10) UNSIGNED NULL DEFAULT NULL,
`sDateInjection` VARCHAR(8) NOT NULL DEFAULT '',
`nNumInjection` VARCHAR(45) NOT NULL DEFAULT '0',
`sNumLot` VARCHAR(45) NOT NULL DEFAULT '',
`nRappel` TINYINT(4) NOT NULL DEFAULT '0',
`sLibelle` VARCHAR(255) NOT NULL DEFAULT '',
`sAction` VARCHAR(16) NOT NULL DEFAULT 'A 20080101000000',
`sDH_REPLIC` DATETIME NULL DEFAULT '2010-01-01 00:00:00',
PRIMARY KEY (`nIdVaccination`),
INDEX `NDX_t_vaccination_nIdUtilisateur` (`nIdUtilisateur`),
INDEX `NDX_t_vaccination_nIdVaccin` (`nIdVaccin`),
INDEX `NDX_t_vaccination_nIdVacProtocole` (`nIdVacProtocole`),
INDEX `NDX_t_vaccination_nIdPatient` (`nIdPatient`),
CONSTRAINT `FK_vaccination_nIdUtilisateur_utilisateur` FOREIGN KEY (`nIdUtilisateur`) REFERENCES `t_utilisateur` (`nIdUtilisateur`),
CONSTRAINT `FK_vaccination_nIdVaccin_vaccin` FOREIGN KEY (`nIdVaccin`) REFERENCES `t_vaccin` (`nIdVaccin`)
)

2 - I would like to insert all the rows in the t_vaccination table of the final database without inserting the duplicates rows. the new query run by inserting one row:

2 - 我想在最终数据库的t_vaccination表中插入所有行,而不插入重复行。通过插入一行来运行新查询:

INSERT INTO t_vaccination (nIdVaccination, nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur, sDateInjection, nNumInjection, sNumLot, nRappel, sLibelle, sAction, sDH_REPLIC) 
SELECT 251,41,4,53,1,'20030528','0','W5770-2',0,'DTP - REVAXIS','A 20130521170623','2013-05-21 17:06:23' FROM t_vaccination  WHERE NOT EXISTS (SELECT nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur FROM t_vaccination WHERE nIdVaccin = 41 and nIdVacProtocole = 4 and nIdPatient = 53 and nIdUtilisateur =1 ) LIMIT 1

3 - Is it possible to insert rows by group by using insert where not exists because the attempts that i have done failed. here is an example of an an insert that failed:

3 - 是否可以通过使用不存在的插入按组插入行,因为我已完成的尝试失败。这是一个失败的插入示例:

INSERT INTO t_vaccination (nIdVaccination, nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur, sDateInjection, nNumInjection, sNumLot, nRappel, sLibelle, sAction, sDH_REPLIC) 
SELECT 251,41,4,53,1,'20030528','0','W5770-2',0,'DTP - REVAXIS','A 20130521170623','2013-05-21 17:06:23' FROM t_vaccination  WHERE NOT EXISTS (SELECT nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur FROM t_vaccination WHERE nIdVaccin = 41 and nIdVacProtocole = 4 and nIdPatient = 53 and nIdUtilisateur =1 ) LIMIT 1,
SELECT 243,NULL,NULL,53,1,'20130525','0','',1,'DTP - ','A 20130521170623','2013-05-21 17:06:23' FROM t_vaccination  WHERE NOT EXISTS (SELECT nIdVaccin, nIdVacProtocole, nIdPatient, nIdUtilisateur FROM t_vaccination WHERE nIdVaccin = NULL and nIdVacProtocole = NULL and nIdPatient = 53 and nIdUtilisateur =1 ) LIMIT 1

I hope for your help.

我希望得到你的帮助。

Regards

Motti

1 个解决方案

#1

In my opinion, the simplest way to do what you want is to remove Unique keys/indexes and remove dupes or create a temporary table without those keys. Let's assume you create a temp_t_vaccination table and import all your rows in, you'll then just have to do :

在我看来,做你想做的最简单的方法是删除唯一键/索引并删除欺骗或创建没有这些键的临时表。假设您创建了一个temp_t_vaccination表并导入了所有行,您只需要这样做:

INSERT INTO t_vaccination (field1, field2 ...) SELECT DISTINCT field1, fields2 ... FROM temp_vaccination

ref : http://dev.mysql.com/doc/refman/5.0/en/insert-select.html?ff=nopfpls

参考:http://dev.mysql.com/doc/refman/5.0/en/insert-select.html?fff = nopfpls