title: Set column value of tableA to the id of tableB based on date (in tableB)
body: <p>I have two tables. First table is called <code>employeeinfo</code> and it looks like this:</p>
  
  <pre><code>______________________________________________________________________
  | Id | FirstName | LastName | Hours | Rate | TotalCost | BatchDateId |
  |____|___________|__________|_______|______|___________|_____________|
  | 1  | John      | Smith    |  30   |  40  |   1200    |      ?      |
  |____|___________|__________|_______|______|___________|_____________|
  | 2  | Jane      | Doe      |  40   | 100  |   4000    |      ?      |
  |____|___________|__________|_______|______|___________|_____________|
  </code></pre>
  
  <p>Second table is called <code>batchdate</code> and it looks like this:</p>
  
  <pre><code>___________________
  | Id |    Date    |
  |____|____________|
  | 1  | 2015-06-25 |
  |____|____________|
  </code></pre>
  
  <p>Notice the <code>"?"</code> in the first table (<code>employeeinfo</code> table).. My goal is to be able to set the <code>BatchDateId</code> as the <code>Id</code> from the second table (<code>batchdate</code> table) based on the date (for example the <code>2015-06-25</code>).</p>
  
  <p>BUT the catch also is, Lets say I run the query again tomorrow, I would like it to update the <code>BatchDateId</code> with the new <code>Id</code> from the <code>batchdate</code> table (because it'll have a new row with a new <code>Id</code> and a new <code>Date</code>).</p>
  
  <p>Thanks in advance!</p>
  
  <p>EDIT: My statements so far are the following:</p>
  
  <pre><code>String employeeInfo = "INSERT INTO employeeinfo (FirstName, LastName, Hours, Rate, TotalCost, BatchDateId) "
                                  + "(?, ?, ?, ?, ?, ?)"
                                  + " ON DUPLICATE KEY UPDATE "
                                  + "Hours=(?), Rate=(?), TotalCost=(?);";
  </code></pre>
  
  <p>And:</p>
  
  <pre><code>String batchDate = "INSERT INTO batchdate (Date) VALUES " 
                  + "(?)" 
                  + " ON DUPLICATE KEY UPDATE " 
                  + "Date=(?);";
  </code></pre>
  
  <p>EDIT2: So what I'm trying to do is, lets say I've updated John Smith's <code>Hours</code> and/or <code>Rate</code> <strong>on a different day</strong> (which will also change the <code>TotalCost</code>), then the <code>BatchDateId</code> should change to the new <code>Id</code> which contains the new <code>Date</code>. If none of the existing rows in <code>employeeinfo</code> are affected <strong>or those rows got updated with in the same day</strong> then the <code>BatchDateId</code> stays the same; however if there are new rows then they should have a different <code>BatchDateId</code> (Based on the new row in <code>batchdate</code> table).</p>