Help with MySQL UPDATE with Subquery

Question

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). 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!!!!

Mchl · Accepted Answer · 2011-01-21 15:04:50Z

2

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

answered Jan 21, 2011 at 15:04

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Help with MySQL UPDATE with Subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related