CODEWITHSUNDEEP
mysqljoinselectsubquerymultiple-columns
studebaker
studebaker

Reputation: 3

MySQL UPDATE with SELECT statement - convert to JOIN

Problem: MySQL - Get the sum of IBB for each PersonID in Batting and update that IBB for each PersonID in Batting_Career

Could someone help me figure out the JOIN?

What I have that is not working:

UPDATE Batting_Career
SET Batting_Career.IBB = Batting.IBB WHERE PersonID IN 
  ( SELECT DISTINCT
        PersonID,
        SUM(IBB) AS IBB
    FROM
        Batting 
     GROUP BY PersonID )
AND Batting_Career.PersonID = Batting.PersonID;

The Batting select statement works (...) - it gets me the sum of IBB in a column IBB for all playerIDs but when I add to the update using it does not work.

The error that I am getting is

Operand should contain 1 column(s)

I believe the rules are You can only select one column from such a query. Which mean I may have to Join the tables. Could anyone help me figure this out?

Upvotes: 0

Views: 75

Answers (2)

forpas
forpas

Reputation: 164174

Aggregate in Batting and join to Batting_Career:

UPDATE Batting_Career bc
JOIN (
  SELECT PersonID, SUM(IBB) as IBB  
  FROM Batting 
  GROUP BY PersonID
) b ON b.PersonID = bc.PersonID
SET bc.IBB = b.IBB;

Upvotes: 1

Muhammad Mujtaba Shafique
Muhammad Mujtaba Shafique

Reputation: 295

I have written a query that will do your work :)

UPDATE Batting_Career t2
SET t2.IBB = t1.IBB where PersonID IN (
   SELECT
        PersonID,
        SUM(IBB) AS IBB
    FROM
        Batting
    WHERE
        PersonID IN(
        SELECT DISTINCT
            PersonID
        FROM
            Batting
    )
GROUP BY
    PersonID
) t1
WHERE
    t2.PersonID = t1.PersonID;

Upvotes: 0

Related Questions