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] Link to comment https://forums.phpfreaks.com/topic/21483-whats-wrong-here/ Share on other sites More sharing options...
fenway Posted September 21, 2006 Share Posted September 21, 2006 You can't put an UPDATE there... Link to comment https://forums.phpfreaks.com/topic/21483-whats-wrong-here/#findComment-95783 Share on other sites More sharing options...
jmaccs64 Posted September 21, 2006 Author Share Posted September 21, 2006 where do i put it???? Link to comment https://forums.phpfreaks.com/topic/21483-whats-wrong-here/#findComment-95787 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.... Link to comment https://forums.phpfreaks.com/topic/21483-whats-wrong-here/#findComment-95790 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 Link to comment https://forums.phpfreaks.com/topic/21483-whats-wrong-here/#findComment-95792 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] Link to comment https://forums.phpfreaks.com/topic/21483-whats-wrong-here/#findComment-96058 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? Link to comment https://forums.phpfreaks.com/topic/21483-whats-wrong-here/#findComment-97130 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. Link to comment https://forums.phpfreaks.com/topic/21483-whats-wrong-here/#findComment-97925 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.