CODEWITHSUNDEEP
mysqlsqlsubquery
csm232s
csm232s

Reputation: 1660

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

Upvotes: 1

Views: 314

Answers (1)

Mchl
Mchl

Reputation: 62369

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

Upvotes: 2

Related Questions