title: How to UPDATE the latest entry for a record in SQL Server 2008 R2
body: <p>I have a table, <code>SUBSCRIBERS</code>, in which I can have more than one entry per subscriber.</p>
  
  <p>(let's say because it stopped being a subscriber and now it became a customer again, so it has a different 'identity' but the same 'subno')</p>
  
  <p>I need an <code>UPDATE</code> statement that updates a column in a row (let's say their address), but only for the latest record for that subscriber (with the latest <code>EffectiveDate</code>). </p>
  
  <p>So I figured out how to select such a row:</p>
  
  <pre><code>SELECT 
      t1.identity, t1.Subno, t1.EffectiveDate
  FROM 
      SUBSCRIBERS t1
  INNER JOIN 
      (SELECT subno,max(EffectiveDate) as LatestDate 
       FROM SUBSCRIBERS
       GROUP BY Subno) t2 ON t1.Subno = t2.Subno AND t1.EffectiveDate = t2.LatestDate
  </code></pre>
  
  <p>But I don't know how to UPDATE the row for a given subno value. Well, I do, but I don't want to do it with 17 nested select statements.</p>
  
  <p>Thanks!</p>