title: Update table conditionally based on two columns and multiple rows in another table
body: <p>I need to update a foreign key in table 1 with the correct entry based on table 2. The correct foreign key is the earliest date that falls after, but not before the next effective dates in table 2. If there are multiple entries in table 2 with the same effective date, then use the modified date column as a tie breaker and pick the most recent one.  Here is the based table structure (all dates are in Date format):</p>
  
  <pre><code>Table 1
  pK1   PeriodStartDate               pK2    
  1     2016-04-01 00:00:00.000               
  2     2016-07-01 00:00:00.000
  
  Table 2
  pK2    EffectiveFrom                ModifiedDate
  3      2016-03-01 00:00:00.000      2016-04-01 00:00:00.000
  4      2016-05-01 00:00:00.000      2016-06-01 00:00:00.000
  5      2016-05-01 00:00:00.000      2016-06-02 00:00:00.000
  </code></pre>
  
  <p>So in the above example table 1 would look like this:</p>
  
  <pre><code>pK1   PeriodStartDate                  pK2    
  1     2016-04-01 00:00:00.000          3     
  2     2016-07-01 00:00:00.000          5
  </code></pre>
  
  <p>This is because for row 1 it falls between March 1st and May 1st (from table 2). And for row 2 it is after the last date, but as there are two similar start dates we choose the last modified.</p>
  
  <p>I'm not sure of the solution. I was trying something like this:</p>
  
  <pre><code>UPDATE table1 
  SET pK2 = table2.pK2
  FROM table2 
  WHERE PeriodStartDate &gt; (SELECT FIRST(table2.EffectiveFrom) FROM table2)
  </code></pre>
  
  <p>I'm just not sure how to find an entry that is bounded by another row (and then needs another column for the tie breaker)</p>