mysql - mysql一条语句同时更新两张表

Question

我有两个表——“事件”和“房间”。

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;

有一个过程可以将新事件的批量导入插入到事件表中。完成后，我们需要使用与“rooms”表中的主键相对应的 roomKey 更新新记录 - 我们可以使用房间中记录的 locationID、buildingID、areaID、roomID 和 clientKey 进行匹配 table 。 (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

但它没有考虑两个帐户(客户)在 location、bldg、area 和 room 中具有相同值的情况。基于另一个帐户具有具有相同 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