Can someone tell me why this query doesn't work?

[bfreshour]

UPDATE tblplayers a,
(
SELECT SUM(COALESCE(co stCP, 0)) AS CPCOUNT, forumMemberID
FROM tblplayers c
LEFT OUTER JOIN tblplayerabilit ies a ON a.playerID = c.forumMemberID
LEFT OUTER JOIN tbldoctrineabil ities b ON a.abilityID = b.abilityID
GROUP BY CP, forumMemberID
) b
SET a.CP = 15 - (b.CPCOUNT+a.CP )
WHERE a.forumMemberID = b.forumMemberID
AND (b.CPCOUNT+a.CP ) < 15

--

I appears to hate the (b.CPCOUNT+a.CP ) portion in the SET statement. If I use an integer there, it works fine. Maybe there's another way to do this?

[bfreshour]

Hmm... I just got it to work by doing the following...

UPDATE tblplayers a,
(
SELECT SUM(COALESCE(costCP, 0)) AS CPCOUNT, forumMemberID
FROM tblplayers c 
LEFT OUTER JOIN tblplayerabilities a ON a.playerID = c.forumMemberID
LEFT OUTER JOIN tbldoctrineabilities b ON a.abilityID = b.abilityID
GROUP BY CP, forumMemberID
) b
SET a.CP = a.CP + (15 - b.CPCOUNT)
WHERE a.forumMemberID = b.forumMemberID
AND (b.CPCOUNT+a.CP) < 15

[ronverdonk]

Good you found it. Next time, enclose your code within code tags. Read the Posting Guideline on that.

moderator