Updating two tables in mysql with one statement

Question

I have two tables- "events" and "rooms".

The rooms table looks like this:

    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;

and the events table looks like this:

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;

There is a process which inserts a bulk import of new events into the events table. Once it is complete, we need to update the new records with the roomKey that corresponds to the primary key in the "rooms" table - which we can match up using the locationID, buildingID, areaID, roomID and clientKey of a record in the rooms table. (clientKey is necessary as multiple accounts could have the same location, bldg, area, or room values).

Currently, the query I use for this is:

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

But it does not take into consideration the instances when two accounts (clients) have the same values in location, bldg, area and room. The query is susceptible to putting the wrong roomKey value in the event record based on the fact that another account has a room record with the same locationID, buildingID, areaID and roomID.

I can modify the query to run like shown below, but it is extremely slow. I know it's not the right query to use, so I am looking to improve it - without having to do this update with a PHP loop, etc.

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

Why are you duplicating locationID, buildingID, areaID, roomID, and clientKey across both tables? It seems you might be well-served to look at normalizing your table structure. — Mike Brant, Sep 10 '12 at 16:14
Valid point, but unfortunately not an option at this point to re-architect the schema. I'm dealing with legacy code that is on a path for sunset. Just need to fix the bug in the existing design at this point. — Brian Bell, Sep 10 '12 at 17:07
So the clientKey value in the rooms table is authoritative, and you just need to match the clientKey value in events to match what is in rooms table based on the roomID? — Mike Brant, Sep 10 '12 at 17:20
imported information has locID, bldgID, areaID, roomID and clientKey. But - need to update the event record with the PK value of the corresponding room table record (roomKey). — Brian Bell, Sep 10 '12 at 17:27

score 0 · Answer 1 · answered Sep 10 '12 at 16:11

0

In Mysql, you can use this syntax when joining on update

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

answered Sep 10 '12 at 16:11

John Woo

258,903
69
498
492

Mike Brant · Answer 2 · 2012-09-10T17:30:09.283

0

If the clientKey value in rooms is authoratative and you just want to update all clientKey values in events based on matching roomID values, then you should be able to do this:

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

edited Sep 10 '12 at 17:30

answered Sep 10 '12 at 17:24

Mike Brant

70,514
10
99
103

Updated to reflect use of `roomKey` as the join field rather than `roomID` – Mike Brant Sep 10 '12 at 17:30

Updating two tables in mysql with one statement

2 Answers2