Jump to content

UPDATE help


esiason14

Recommended Posts

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

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

[!--quoteo(post=371833:date=May 6 2006, 12:13 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 6 2006, 12:13 PM) [snapback]371833[/snapback][/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:

[code]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[/code]

Hitterstats table currently looks like this:

[code]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     [/code]

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:

[code]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[/code]

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

[!--quoteo(post=372133:date=May 7 2006, 05:14 PM:name=esiason14)--][div class=\'quotetop\']QUOTE(esiason14 @ May 7 2006, 05:14 PM) [snapback]372133[/snapback][/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.
Link to comment
Share on other sites

[!--quoteo(post=372151:date=May 7 2006, 06:23 PM:name=esiason14)--][div class=\'quotetop\']QUOTE(esiason14 @ May 7 2006, 06:23 PM) [snapback]372151[/snapback][/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?
Link to comment
Share on other sites

[!--quoteo(post=372162:date=May 7 2006, 08:41 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 7 2006, 08:41 PM) [snapback]372162[/snapback][/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:

[code]"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
[/code]

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?

Link to comment
Share on other sites

[!--quoteo(post=372166:date=May 7 2006, 08:39 PM:name=esiason14)--][div class=\'quotetop\']QUOTE(esiason14 @ May 7 2006, 08:39 PM) [snapback]372166[/snapback][/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:

[code]"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
[/code]

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.
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.