jmaccs64 Posted September 21, 2006 Share Posted September 21, 2006 I am new at this....can someone please tell me what is wrong with this statement?Thanks[code]SELECT users.username, users.vote_count, surpool06.surpool_username, surpool06.surpool_teamFROM usersINNER JOIN surpool06ON users.username = surpool06.surpool_usernameUPDATEusers SET vote_count = IF(surpool_team = 'Bears',vote_count-1,vote_count)[/code] Quote Link to comment Share on other sites More sharing options...
fenway Posted September 21, 2006 Share Posted September 21, 2006 You can't put an UPDATE there... Quote Link to comment Share on other sites More sharing options...
jmaccs64 Posted September 21, 2006 Author Share Posted September 21, 2006 where do i put it???? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 21, 2006 Share Posted September 21, 2006 I'm not really sure what you're trying to do.... Quote Link to comment Share on other sites More sharing options...
jmaccs64 Posted September 21, 2006 Author Share Posted September 21, 2006 Join two table users and surpool06then based on a field in surpool06 subtract 1 from vote count on users...Does that make sense?Thanks Quote Link to comment Share on other sites More sharing options...
fenway Posted September 21, 2006 Share Posted September 21, 2006 Provided you're using a recent version of MySQL, UPDATE statements support multiple tables (UNTESTED):[code]UPDATE users INNER JOIN surpool06 ON users.username = surpool06.surpool_usernameSET vote_count = IF(surpool_team = 'Bears',vote_count-1,vote_count)[/code] Quote Link to comment Share on other sites More sharing options...
jmaccs64 Posted September 23, 2006 Author Share Posted September 23, 2006 Thanks...that works...but there is one problem...Although each vote has a unique identifier.. if the person picks the same team twice in the same week, it only subtracts 1, from their vote count, when it should subtract 2....Is there any way to remedy this? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 25, 2006 Share Posted September 25, 2006 Sounds like you'd need a GROUP BY, and subtract the count. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.