update possible?

[esiason14]

I have two tables, one which I am trying to update based on the other.
[b]
temp table[/b]
id
year
position_id
games

[b]players[/b]
id
year
position_id



Here is the problem...the temp can hold several records per player and even multiple for each year...For example:
[b]player_id, year, position_id, games[/b]
4323, 2005, 5, 66
4323, 2005, 4, 20
4323, 2005, 3, 7

This example is for the player_id (4323) for 2005. This player played three different positions (3,4,5). What I want to do , if possible, is to find the position that player played the most games at for that given year and update the players table with that position_id.

[fenway]

Well, you can find the desired position_id as follows (UNTESTED):

[code]SELECT position_id, COUNT(games) AS cnt FROM players GROUP BY player_id ORDER by cnt DESC LIMIT 1[/code]

So it should be easily to convert this to an update.

[esiason14]

Hi Fenway,
I've been trying to tweak the query you posted, but haven't been able to get the results I'm looking for.

If I use this query

[code]SELECT position_id, COUNT( games ) AS cnt
FROM temp
GROUP BY player_id
ORDER BY cnt DESC
LIMIT 1;[/code]


it returns the position_id 3 with a count of 94.


I guess I need a little more help, so I can isolate the count to a specific player for a specific year

[wickning1]

This will require MySQL 4.1 or greater. If you are using an older version, you will have to split it up into a couple queries.

[code]UPDATE players p
INNER JOIN (
   SELECT t.id, MIN(t.position_id) as position_id, t.year
   FROM temptable t
   INNER JOIN (
      SELECT id, year, MAX(games) as mgames
      FROM temptable GROUP BY id, year
   ) s ON s.mgames=t.games AND s.id=t.id AND s.year=t.year
   GROUP BY t.id, t.year
) f ON p.id=f.id AND p.year=f.year
SET p.position_id=f.position_id[/code]

There remains one question of behavior - If I played shortstop and second base exactly 30 games each in 2005, which position goes in the players table? I set it up there to take the MIN position, so whichever position has a lower position_id will take preference.

[esiason14]

Thanks for the help, unfortunately I'm stuck with 4.0.26 :(