Jump to content


Photo

update possible?


  • Please log in to reply
4 replies to this topic

#1 esiason14

esiason14
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 13 April 2006 - 10:58 PM

I have two tables, one which I am trying to update based on the other.

temp table

id
year
position_id
games

players
id
year
position_id



Here is the problem...the temp can hold several records per player and even multiple for each year...For example:
player_id, year, position_id, games
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.

#2 fenway

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

Posted 14 April 2006 - 05:58 AM

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

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

So it should be easily to convert this to an update.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 esiason14

esiason14
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 14 April 2006 - 04:40 PM

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

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


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

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 14 April 2006 - 08:46 PM

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.

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

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.

#5 esiason14

esiason14
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 15 April 2006 - 02:23 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users