Jump to content

Archived

This topic is now archived and is closed to further replies.

esiason14

update possible?

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.