By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,965 Members | 1,702 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,965 IT Pros & Developers. It's quick & easy.

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

P: 3
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 = 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?
Feb 28 '08 #1
Share this Question
Share on Google+
2 Replies


P: 3
Hmm... I just got it to work by doing the following...
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblplayers a,
  2. (
  3. SELECT SUM(COALESCE(costCP, 0)) AS CPCOUNT, forumMemberID
  4. FROM tblplayers c 
  5. LEFT OUTER JOIN tblplayerabilities a ON a.playerID = c.forumMemberID
  6. LEFT OUTER JOIN tbldoctrineabilities b ON a.abilityID = b.abilityID
  7. GROUP BY CP, forumMemberID
  8. ) b
  9. SET a.CP = a.CP + (15 - b.CPCOUNT)
  10. WHERE a.forumMemberID = b.forumMemberID
  11. AND (b.CPCOUNT+a.CP) < 15
Feb 28 '08 #2

ronverdonk
Expert 2.5K+
P: 4,258
Good you found it. Next time, enclose your code within code tags. Read the Posting Guideline on that.

moderator
Feb 28 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.