title: MySQL Automatic Updater from CSV
body: <p>I am trying to create an automatic score updater for a football database. My client will be emailing me CSV spreadsheets with the game date, home team, home score, visiting team, and visiting score in them. The problem is that I have a separate team table (team_id, team_name, etc.) and game table as outlined below:</p>
  
  <p>team (team_id, team_name, etc.)</p>
  
  <p>game(game_id, game_home_team, game_home_score, game_visitor_team, game_visitor_score, game_date, game_complete)</p>
  
  <p>sample game data(1, 123, 0, 156, 0, 8-31-2012, No) -> I store the team's id instead of their name in the game table.</p>
  
  <p>I need to write a script that will update the scores automatically. There will be about 327 scores per week.</p>
  
  <p>The CSV will look like the following (with column headings):</p>
  
  <p>game_date, home_team, home_score, visitor_team, visitor_score</p>
  
  <p>(2012-8-31, Forest Hills, 44, Westmont Hilltop, 0) -> sample data</p>
  
  <p>How would I first get the team id's, then match them home and visitor id's with their team names, then get the game id that corresponds to the matching team_id's, then update the scores for that game? That's a lot and I am kind of lost on the issue. Any help or even a better way to do this would be greatly appreciated. Below is what i have so far:</p>
  
  <pre><code>UPDATE game SET 
      game_home_score = ?, 
      game_visitor_score = ?, 
      game_complete = 'Yes' 
  WHERE game_id = 
      SELECT game_id 
      FROM game 
      WHERE game_home_team IN (
          SELECT team_id 
          FROM team 
          WHERE team_name = ?
      ) AND game_visitor_team IN (
          SELECT team_id 
          FROM team 
          WHERE team_name = ?
      );
  </code></pre>
  
  <p>I know that query is wrong, but it's what I was thinking as far as the direction I need to go. My MySQL knowledge isn't quite vast enough (I don't think) to get this working.</p>