I am desperately looking to add columns for salary, height and weight to a merged MLBAM atbats and pitches table for all pitchers and batters using MySQL.

I did locate salaries information from the Lahman database files and created a table from it. But since its players comprise only a subset of those included in the merged MLBAM atbats and pitches table, when I try to add a salary column (for either pitchers or batters) to the merged table from the salaries table using the following code, I'm left with a significant number of rows with NULL values for the salary column:

UPDATE `atbats_pitches_python_new` t1
INNER JOIN salaries t2 ON (t2.mlb_name = t1.mlb_name AND t2.year = t1.year AND t2.team_id = t1.team_id)
SET t1.salary=t2.salary

height and weight: I created a 'master' table from the Lahman files with height and weight columns; a table from Stattle with both height and weight columns; and the MLBAM atbats table with b_height that contain batter heights. b_height column from the 'atbats' table does contain heights, and I am currently running the query to add that column to the merged atbats_pitches_python_new table, but after adding a weight column from both the 'master' table and then the 'players_Stattle' table to add players data unique to each of the two tables, I'm again left with many NULL values for weight.

Can someone please let me know what I should do differently with the files I have in order to include salary, height, and weight for all players in the merged atbats/pitches table and have no NULL or blank values for those three fields? Is there another source for that information that I'm not using? How are others handling this dilemma?

Thank you in advance.

  • Hm, shouldn't you use LEFT JOIN in your query instead of INNER JOIN? – Stanislav Kralin Nov 26 '17 at 21:44
  • Thanks for your response--maybe so. I'll try LEFT JOIN, but not sure if thre will even be a tangible difference since the atbats_pitches_python_new table (table B) has more distinct mlb_id values than the mlb_id values of the salaries table (table B). If so, wouldn't the intersection with an inner join achieve the same effect of adding all the values for the players (mlb_id) from the salaries table shared by the players (mlb_ids) from the atbats_pitches_python_new table? – LeeZee Nov 26 '17 at 22:10
  • I mean, If the distinct mlb_id values from the salaries table are only a smaller subset of the distinct mlb_id values of the atbats_pitches_python_new table, wouldn't the intersection of an INNER JOIN achieve the same effect as a LEFT JOIN? – LeeZee Nov 26 '17 at 22:54
  • Well, could you please paste a representative sample of your data to sql-fiddle.com and then describe the problem very concretely (in this way: Noah Syndergaard's weight in 2015 is NULL). – Stanislav Kralin Nov 27 '17 at 4:43
  • Isn't this a programming question? If so, please flag and we move to SO – philshem Sep 2 '18 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.