CODEWITHSUNDEEP
mysqlinvalid-argument
Matt W
Matt W

Reputation: 12423

Why am I getting 'invalid use of group function' error in mysql update?

This SQL works in parts, but put all together I get an error which is simply:

Invalid use of group function

I assume it is referring to the UPDATE statement itself, as I'm not using a GROUP statement, but I don't know why.

My query:

UPDATE `Scores` s
INNER JOIN `Players` p ON s.PlayerID = p.PlayerID
INNER JOIN (SELECT PlayerID, Number FROM `NumberHistory` h WHERE PlayerID = 136 ORDER BY Created DESC LIMIT 1) f ON p.PlayerID = f.PlayerID
INNER JOIN (
    SELECT Number FROM `Players` WHERE PlayerID = 136
    UNION ALL
    SELECT Number FROM `NumberHistory` WHERE PlayerID = 136
) m
LEFT OUTER JOIN (
    SELECT PlayerID, COUNT(PlayerID) Activity
    FROM `NumberHistory` WHERE Created BETWEEN DATE_ADD(NOW(), INTERVAL -7 DAY) AND NOW()
) a ON p.PlayerID = a.PlayerID
SET s.PlayerID=p.PlayerID, s.PlayerName=p.PlayerName, s.Number=MAX(m.Number), s.PreviousNumber=f.Number, s.Diff=(p.Number-IFNULL(f.Number,0)), s.Activity=IFNULL(a.Activity, 0)
WHERE s.PlayerID = 136

Upvotes: 0

Views: 377

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You can't just use max() like that in set. Adding ORDER BY Number DESC LIMIT 1 to the m subquery might do what you want:

UPDATE `Scores` s INNER JOIN
       `Players` p
       ON s.PlayerID = p.PlayerID INNER JOIN
       (SELECT PlayerID, Number
        FROM `NumberHistory` h
        WHERE PlayerID = 136
        ORDER BY Created DESC
        LIMIT 1
       ) f
       ON p.PlayerID = f.PlayerID INNER JOIN
       (SELECT Number FROM `Players` WHERE PlayerID = 136
        UNION ALL
        SELECT Number FROM `NumberHistory` WHERE PlayerID = 136
        ORDER BY Number DESC
        LIMIT 1
       ) m LEFT OUTER JOIN
       (SELECT PlayerID, COUNT(PlayerID) as Activity
        FROM `NumberHistory`
        WHERE Created BETWEEN DATE_ADD(NOW(), INTERVAL -7 DAY) AND NOW()
       ) a
       ON p.PlayerID = a.PlayerID
 SET s.PlayerID = p.PlayerID,
     s.PlayerName = p.PlayerName, 
     s.Number = m.Number,
     s.PreviousNumber = f.Number,
     s.Diff = (p.Number-IFNULL(f.Number,0)),
     s.Activity = IFNULL(a.Activity, 0)
WHERE s.PlayerID = 136

Upvotes: 1

Related Questions