UPDATE help

[esiason14]

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.

[fenway]

I think you need to combine these tables and then keep track of which ones were pitchers to begin with.

[esiason14]

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?

[fenway]

Then I don't understand... aren't these stats totally different?

[esiason14]

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

[fenway]

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

[esiason14]

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.

[fenway]

[!--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?

[esiason14]

[!--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?

[fenway]

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