sql - 用最高值批量替换更多表中的值

Question

我有一个包含 OWN_ID 和 OWN_Email 的表 -

own_id      |   own_email
-----------------------------------------------
3ace7cf80edd    |   email@example.com
3acf6af33ff7    |   email@example.com
3acda2524e00    |   email@example.com
3ad75583c9a7    |   spam@example.com
3ad74b018999    |   spam@example.com

等等

问题是每个电子邮件应该只包含一个 ID，我还需要用 OWN_Email 的最高 OWN_ID 值替换另一个表中的所有 OWN_ID 值

sql 创建:

CREATE TABLE  `blahblah.`eventowner` (
  `OWN_ID` varchar(12) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
  `OWN_Email` varchar(120) COLLATE utf8_czech_ci DEFAULT NULL,
  `OwnDateFormat` varchar(16) COLLATE utf8_czech_ci DEFAULT NULL,
  `OwnWeekStart` int(11) DEFAULT NULL,
  `OwnDayStart` int(11) DEFAULT NULL,
  `OwnDayEnd` int(11) DEFAULT NULL,
  PRIMARY KEY (`OWN_ID`),
  KEY `OwnerEmailIndex` (`OWN_Email`),
  KEY `OwnerIndex` (`OWN_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci

CREATE TABLE  `blahblah`.`event` (
  `EVN_ID` varchar(128) COLLATE utf8_czech_ci DEFAULT NULL,
  `EVNGRP_ID` varchar(12) COLLATE utf8_czech_ci DEFAULT NULL,
  `EVNOWN_ID` varchar(12) COLLATE utf8_czech_ci DEFAULT NULL,
  `EVNRCR_ID` varchar(12) COLLATE utf8_czech_ci DEFAULT NULL,
  `Evn_EditCounter` int(11) DEFAULT NULL,
  `Evn_Created` int(11) DEFAULT NULL,
  `Evn_Modified` int(11) DEFAULT NULL,
  `EvnFolder` varchar(128) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnTitle` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnNote` text COLLATE utf8_czech_ci,
  `EvnLocation` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnPriority` int(11) DEFAULT NULL,
  `EvnComplete` int(11) DEFAULT NULL,
  `EvnColor` varchar(2) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnClass` varchar(1) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnShareType` varchar(1) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnTimeFormat` varchar(1) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnType` varchar(127) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnStatus` varchar(1) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnOrganizer` varchar(80) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnContact` varchar(80) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnURL` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnStartDate` int(11) DEFAULT NULL,
  `EvnStartTime` int(11) DEFAULT NULL,
  `EvnEndDate` int(11) DEFAULT NULL,
  `EvnEndTime` int(11) DEFAULT NULL,
  `EvnRID` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnUID` varchar(255) COLLATE utf8_czech_ci DEFAULT NULL,
  `EvnExpire` int(11) DEFAULT NULL,
  `EvnSequence` int(11) DEFAULT NULL,
  `EvnFlags` int(11) DEFAULT NULL,
  KEY `EventGroupClassIndex` (`EVNGRP_ID`,`EvnClass`),
  KEY `EventGroupFolderIndex` (`EVN_ID`,`EVNGRP_ID`,`EvnFolder`),
  KEY `EventRIDIndex` (`EvnRID`),
  KEY `EventUIDIndex` (`EvnUID`),
  KEY `EventGroupRCRIndex` (`EVN_ID`,`EVNGRP_ID`,`EVNRCR_ID`),
  KEY `EventExpireIndex` (`EvnExpire`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci

Answer 1

从表t1、表t2中删除t1
其中 t1.own_email= t2.own_email
和 t1.own_id < t2.own_id

更新其他表，表
设置 othertable.own_id= table.own_id
其中 othertable.own_email= table.own_email;