Getting around not Updating across multiple tables

I just found out that MySql currently does not support updating one table based upon values in another table (this was hard for me to believe because I'm fairly new to this, and I just assumed this would be supported).

So, now I have to get around it. i've seen references to being able to get aroung this problem but have never seen it explained.

I have one table TOP10, that has people's votes for their top10 (login, number1, number2, number3, etc). The other table CHOICES has a list of the choices for the Top10. Basically, I want to come up with an aggregate top10 by assigning points to each of the choices in CHOICES Based upon the votes in TOP10. I was hoping to just do: update choices set choices.points=choices.point+10 where TOP10.number1=choices.choices; and then repeat this for numbers2-9. But I can't do this, so how do I get around it? Any suggestions would be appreciated? And if you have a generic solution to this problem please feel free to post that, as all I really need to do is get around the problem of not being able to update based upon values in two tables.

Thank you.

one more thing. I'm using mysql 3.22.25 so I can't create temporary tables from select statements.

This should be relatively simple but it would help to know how you have your form set up.

Also, do you want to update the choices table whenever a vote is processed or periodically?

My form already is dynamic in the sense that the list where they vote for each of their top10 is pulled from the choices table. There is a way to enter a choice not listed but then it is added to the choice table. All the voter sees is a top10 tabe with drop down menus with a list of each of the choices.

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
This should be relatively simple but it would help to know how you have your form set up.

Also, do you want to update the choices table whenever a vote is processed or periodically?[/quote]

Let me rephrase. How are the selections being passed? IOW, what variable name(s) are you using or are you using an array?

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
Let me rephrase. How are the selections being passed? IOW, what variable name(s) are you using or are you using an array?[/quote]

WHen they vote I am using an array. It's called team[0] to team[9]. I then take these values and put them into the database. The database has variables team1-team10. I think I see where you are headed to update the scores with each vote instead of doing it at the end like I am trying to do now.

You should set up your Top10 table like this:

userid | teamid | rank

This way each userid would have ten entries. Use an inverse for storing the rank (ie if the ranked a team #1 then give it a score of 10 in rank).

Now, to get the ranks you can do this:

select sum(rank) as total from top10 group by teamid;

to store this in your choices table, assuming that the teamid column is unique, you can do this instead:

replace into choices (teamid,score) select teamid,sum(rank) from top10 group by teamid;

Thanks!