Jump to content


Photo

UPDATE help


  • Please log in to reply
9 replies to this topic

#1 esiason14

esiason14
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 06 May 2006 - 02:52 AM

Hi, I currently have two tables: hitterstats and pitcherstats. I would like to combine these into one table called playerstats.

Here's my dilemma. Some pitchers (all of whom, obviously, have stats in the pitchers stats table) also have hitting stats in the hitter_stats table, but some dont. So, i need something like this.

If the pitchers player_id exists in the hitterstats table for a certain year, then insert the stats into that yearly record. If there is no record for that player_id, then create a new record for that pitchers players_id. im having trouble coming up with this one, so any advice would be appreciated.

#2 fenway

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

Posted 06 May 2006 - 04:32 AM

I think you need to combine these tables and then keep track of which ones were pitchers to begin with.
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 06 May 2006 - 04:58 AM

True, I should have...but I currently have 25+ years of stats and over 45000 stat records. I know the position of each player, thats not the problem...its the combining of the tables thats the problem. Is there a way to do this?

#4 fenway

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

Posted 06 May 2006 - 04:13 PM

Then I don't understand... aren't these stats totally different?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 esiason14

esiason14
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 07 May 2006 - 10:14 PM

[!--quoteo(post=371833:date=May 6 2006, 12:13 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 6 2006, 12:13 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Then I don't understand... aren't these stats totally different?
[/quote]

Yes, they are totally different. I'll try to explain a little better. Some pitchers also rack up hitting stats (those in the National league), so these pitchers currently have stats in the pitcherstats table and hitterstats table.

Pitcherstats table looks like this:

player_id,year,mlbteam_id,league_id,position_id,games,wins,losses,ip,bfp,ha,hra,bb,ibb,wp,hbp,ko,era,k9,sobb,saves,sho,er,ra,gs,cg,gf,whip,babip

Hitterstats table currently looks like this:

player_id ,year,mlbteam_id ,position_id,league_id ,games,ab,runs,hits,doubles,triples,hr,rbi,sb,cs,walks,so,avg,slg,obp,ops     

What I want to do is to combine these tables so that I have one master table...So if a pitcher has no hitting stats, his hitting stats would be 0, but if he does...his hitting and pitching stats would be filled for that particular year record.

So the playerstats table would look like this:

player_id,year,mlbteam_id,league_id,position_id,games,ab,runs,hits,doubles,triples,hr,rbi,sb,cs,walks,so,avg,slg,obp,ops ,wins,losses,ip,bfp,ha,hra,bb,ibb,wp,hbp,ko,era,k9,sobb,saves,sho,er ,ra,gs,cg,gf,whip,babip

In order to combine these two tables, I have to do a few things:

1) Alter the hitterstats table to include pitcherstats (done)
2) Find the pitchers (by yearly record) who have hitterstats and insert there pitching stats into that recorc
3) Find the pitchers who have no hitterstats (most likely American league pitchers) and create a whole new record in the table.

#6 fenway

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

Posted 07 May 2006 - 10:27 PM

[!--quoteo(post=372133:date=May 7 2006, 05:14 PM:name=esiason14)--][div class=\'quotetop\']QUOTE(esiason14 @ May 7 2006, 05:14 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
In order to combine these two tables, I have to do a few things:

1) Alter the hitterstats table to include pitcherstats (done)
2) Find the pitchers (by yearly record) who have hitterstats and insert there pitching stats into that recorc
3) Find the pitchers who have no hitterstats (most likely American league pitchers) and create a whole new record in the table.
[/quote]
I don't understand why you want to mix these data -- it doesn't make any sense to have a empty pitcher record in the hitters table. You can always find the "missing" ones with LEFT JOINs. And combining them into a single table means that half of the columns are always empty, which is very poor table design. I still don't understand the motivation for taking two perfectly separate but related tables and merging them.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 esiason14

esiason14
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 07 May 2006 - 11:23 PM

It probably is very poor table design, but I have several reasons why I want to do this. For the most part, it will make it a lot easier on me to update.

#8 fenway

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

Posted 08 May 2006 - 12:41 AM

[!--quoteo(post=372151:date=May 7 2006, 06:23 PM:name=esiason14)--][div class=\'quotetop\']QUOTE(esiason14 @ May 7 2006, 06:23 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
It probably is very poor table design, but I have several reasons why I want to do this. For the most part, it will make it a lot easier on me to update.
[/quote]
How so, if you don't mind my asking?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 esiason14

esiason14
  • Members
  • PipPipPip
  • Advanced Member
  • 58 posts

Posted 08 May 2006 - 01:39 AM

[!--quoteo(post=372162:date=May 7 2006, 08:41 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 7 2006, 08:41 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
How so, if you don't mind my asking?
[/quote]

Sure, no problem. I finally found a source where I can get in-season csv files that I can use to update my stats.

A sample record is below (this is for a pitcher with both hitting and pitching stats:

"Pedro Martinez",NYM,18,6,14,0,1,0,0,0,1,1,1,5,0,0,.071,.133,.071,.205,6,5,0,0,0,0,0,39.2,22,13,12,6,12,42,2.72,0.86,.163

Now, maybe I can figure out a way to parse csv and insert the hitting stats into the hitters table and the pitching stats into the pitching table, but I'm not that saavy. Do you think that would be a better solution...if thats even possible?



#10 fenway

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

Posted 08 May 2006 - 02:00 AM

[!--quoteo(post=372166:date=May 7 2006, 08:39 PM:name=esiason14)--][div class=\'quotetop\']QUOTE(esiason14 @ May 7 2006, 08:39 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Sure, no problem. I finally found a source where I can get in-season csv files that I can use to update my stats.

A sample record is below (this is for a pitcher with both hitting and pitching stats:

"Pedro Martinez",NYM,18,6,14,0,1,0,0,0,1,1,1,5,0,0,.071,.133,.071,.205,6,5,0,0,0,0,0,39.2,22,13,12,6,12,42,2.72,0.86,.163

Now, maybe I can figure out a way to parse csv and insert the hitting stats into the hitters table and the pitching stats into the pitching table, but I'm not that saavy. Do you think that would be a better solution...if thats even possible?
[/quote]
Well, the best way to do this would be to dump this info into a temp table that does have the "combined" fields, BUT then to copy over the hitting/pitching stats to your actual tables.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users