CREATE TABLE `libavtorname_copy` (
`AvtorId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR(99) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`MiddleName` VARCHAR(99) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`LastName` VARCHAR(99) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`NickName` VARCHAR(33) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
`uid` INT(11) NOT NULL DEFAULT '0',
`Email` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
`Homepage` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci',
`Gender` CHAR(1) NOT NULL DEFAULT '' COLLATE 'utf8_unicode_ci',
`MasterId` INT(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`AvtorId`) USING BTREE,
INDEX `FirstName` (`FirstName`(20)) USING BTREE,
INDEX `LastName` (`LastName`(20)) USING BTREE,
INDEX `email` (`Email`) USING BTREE,
INDEX `Homepage` (`Homepage`) USING BTREE,
INDEX `uid` (`uid`) USING BTREE,
INDEX `MasterId` (`MasterId`) USING BTREE
) COLLATE 'utf8_unicode_ci' ENGINE=MyISAM ROW_FORMAT=Dynamic AUTO_INCREMENT=265793;
INSERT INTO `libavtorname_copy` (`AvtorId`, `FirstName`, `MiddleName`, `LastName`, `NickName`, `uid`, `Email`, `Homepage`, `Gender`, `MasterId`) SELECT `AvtorId`, `FirstName`, `MiddleName`, `LastName`, `NickName`, `uid`, `Email`, `Homepage`, `Gender`, `MasterId` FROM `libavtorname`;
-- пишем строку для сравнения
UPDATE libavtorname_copy SET EMail = TRIM(CONCAT_WS(' ',TRIM(LastName),TRIM(FirstName),TRIM(MiddleName)));
-- пропись Nickname в Отчество для дублей авторов, где оно есть
UPDATE libavtorname SET MiddleName=TRIM(CONCAT(TRIM(`MiddleName`),' (',TRIM(`NickName`),')')) WHERE NickName!='' and AvtorId IN (SELECT DISTINCT AvtorId FROM libavtorname_copy WHERE MasterId=0 AND
EMail IN (SELECT EMail FROM libavtorname_copy WHERE MasterId=0 GROUP BY EMail HAVING COUNT(*) > 1) ORDER BY EMail);
DROP TABLE `libavtorname_copy`;