Jump to content

update possible?


esiason14

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.