Jump to content


Photo

Whats wrong here?


  • Please log in to reply
7 replies to this topic

#1 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 21 September 2006 - 12:46 AM

I am new at this....can someone please tell me what is wrong with this statement?
Thanks


SELECT users.username, users.vote_count, surpool06.surpool_username, surpool06.surpool_team
FROM users
INNER JOIN surpool06
ON users.username = surpool06.surpool_username
UPDATE
users SET vote_count = IF(surpool_team = 'Bears',vote_count-1,vote_count)


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 12:49 AM

You can't put an UPDATE there...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 21 September 2006 - 12:50 AM

where do i put it????

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 12:50 AM

I'm not really sure what you're trying to do....
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 21 September 2006 - 12:52 AM

Join two table users and surpool06

then based on a field in surpool06 subtract 1 from vote count on users...

Does that make sense?
Thanks

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 01:22 PM

Provided you're using a recent version of MySQL, UPDATE statements support multiple tables (UNTESTED):
UPDATE users 
INNER JOIN surpool06 
ON users.username = surpool06.surpool_username
SET vote_count = IF(surpool_team = 'Bears',vote_count-1,vote_count)

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 jmaccs64

jmaccs64
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 23 September 2006 - 05:42 AM

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?



#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 03:13 AM

Sounds like you'd need a GROUP BY, and subtract the count.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users