title: Update multiple rows with max date for each foreign key
body: <p>I can select the information for the row(s) I want to update using this statement:</p>
  
  <pre><code>select max(Date),F_ID from MyTable group by F_ID
  </code></pre>
  
  <p>F_ID is a foreign key. I'm trying to update a different field where the date is at the maximum. The issue is that there may be many rows with the same foreign key and the same date. In the case that these are the max date has multiple rows I want to update all of them.</p>
  
  <p>The closest I've been able to get is this:</p>
  
  <pre><code>update MyTable set
  Curr = 1
  where (select cast(Date as varchar(20))+cast(F_ID as varchar(10)) from MyTable) in
  (select cast(max(Date)as varchar(20))+cast(F_ID as varchar(10)) from MyTable group by A_PID)
  </code></pre>
  
  <p>I get this error:</p>
  
  <pre><code>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.
  The statement has been terminated.
  </code></pre>
  
  <p>Thanks for the help!</p>
  
  <p><strong>Edit: Final query format for the update statement in case someone else is looking for this:</strong></p>
  
  <pre><code>update MyTable set
      Curr = 1
  from MyTable t inner join
      (select F_ID,max(Date) maxDate
       from MyTable group by A_PID) t2
  on t.A_PID = t2.A_PID and t.Date = t2.maxDate
  </code></pre>
  
  <p>Thanks to @Jim</p>