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 salary 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?

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