用一条语句更新mysql中的两个表

Question

我有两张桌子——"events"one_answers"rooms"。

房间的桌子是这样的：

    CREATE TABLE `rooms` (
    `roomKey` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
    `locationID` INT(9) NOT NULL DEFAULT '0',
    `locationName` VARCHAR(150) NOT NULL DEFAULT '',
    `buildingID` INT(9) NOT NULL DEFAULT '0',
    `buildingName` VARCHAR(150) NOT NULL DEFAULT '',
    `areaID` INT(9) NOT NULL DEFAULT '0',
    `areaName` VARCHAR(150) NOT NULL DEFAULT '',
    `roomID` INT(9) NOT NULL DEFAULT '0',
    `roomName` VARCHAR(150) NOT NULL DEFAULT '',
    `clientKey` MEDIUMINT(5) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`roomKey`),
    UNIQUE INDEX `locationID` (`locationID`, `buildingID`, `areaID`, `roomID`, `clientKey`),
    INDEX `clientKey` (`clientKey`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=21270411;

事件表如下所示：

CREATE TABLE `events` (
    `eventKey` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `accountNum` INT(11) NULL DEFAULT NULL,
    `locationID` INT(9) NOT NULL DEFAULT '0',
    `buildingID` INT(9) NOT NULL DEFAULT '0',
    `areaID` INT(9) NOT NULL DEFAULT '0',
    `roomID` INT(9) NOT NULL DEFAULT '0',
    `clientKey` SMALLINT(5) UNSIGNED NOT NULL,
    `roomKey` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `eventTitle` VARCHAR(100) NOT NULL DEFAULT '',
    `dateStart` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `dateEnd` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `status` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`eventKey`),
    UNIQUE INDEX `uniqueEvent` (`accountNum`, `locationID`, `buildingID`, `areaID`, `roomID`),
    INDEX `clientKey` (`clientKey`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=98690419;

有一个过程将新事件的批量导入插入到事件表中。一旦完成，我们需要使用与"房间"表中的主键相对应的roomKey更新新记录——我们可以使用房间表中记录的locationID、buildingID、areaID、roomID和clientKey进行匹配。（clientKey是必需的，因为多个帐户可能具有相同的位置、建筑、面积或房间值）。

目前，我对此使用的查询是：

UPDATE events e, rooms dr SET e.locationKey=dr.locationKey 
       WHERE e.locationID=dr.locationID 
           AND e.buildingID=dr.buildingID 
           AND e.areaID=dr.areaID 
           AND e.roomID=dr.roomID 
           AND e.clientKey=dr.clientKey

但它没有考虑两个帐户（客户）在位置、建筑、面积和房间方面具有相同值的情况。由于另一个帐户的房间记录具有相同的locationID、buildingID、areaID和roomID，因此该查询很容易在事件记录中输入错误的roomKey值。

我可以将查询修改为如下所示运行，但速度非常慢。我知道这不是一个合适的查询，所以我希望改进它——而不必使用PHP循环等进行更新。

UPDATE EVENTS e, rooms dr SET e.locationKey=dr.locationKey
WHERE (e.locationID=dr.locationID AND e.clientKey=dr.clientKey)
    AND (e.buildingID=dr.buildingID  AND e.clientKey=dr.clientKey)
    AND (e.areaID=dr.areaID  AND e.clientKey=dr.clientKey)
    AND (e.roomID=dr.roomID  AND e.clientKey=dr.clientKey)
    AND e.clientKey=dr.clientKey

Answer 1

在Mysql中，在update 上加入时可以使用此语法

UPDATE table1 t1 
       INNER JOIN table2 t2 
         ON t1.id = t2.id
SET t1.col1 = t2.col2, t1.col2 = t2.col2

Answer 2

如果rooms中的clientKey值是授权的，并且您只想根据匹配的roomID值更新events中的所有clientKey值，那么您应该能够执行以下操作：

UPDATE rooms AS r
INNER JOIN events AS e ON r.roomKey = e.roomKey
SET e.clientKey = r.clientKey