Help with MySQL UPDATE with Subquery

Need some help with this one... I need to update a column in one of my tables which was getting populated with 0 instead of the correct value. There's thousands of entries and I want to attempt to do this through a SQL script rather than PHP.

As an example, the player table consists of the columns (playerID, teamID, cityID, and stateID). stadium table has (statiumID, teamID, cityID, and stateID开发者_如何学Go). The cityID in Table2 was set to 0 with some incorrect code. I know I can resolve this with a subquery, but I'm relatively new to the concept. I've come up with the following, but I have the feeling it's not very optimized:

UPDATE
`database1`.`stadium`
SET
`stadium`.`cityID` = 
(
    SELECT
        `player`.`cityID`
    FROM
        `database2`.`player`
    WHERE
        `player`.`teamID` = `stadium`.`teamID`
    AND
        `player`.`stateID` = `stadium`.`stateID`
)
WHERE
`stadium`.`cityID` = 0;

I'm fairly certain this statement isn't optimized and could be cleaned up. Any help would be greatly appreciated!!!!

---

I think you want something like this.

UPDATE
  database1.stadium AS s
INNER JOIN (
  SELECT DISTINCT teamID, stateID, cityID FROM database2.player
) AS p
USING (teamID, stateID)
SET
  s.cityID = p.cityID
WHERE
  s.cityID = 0