title: t-sql update multiple rows using inner join
body: <p>i trying to update a column in my table for several rows at the same time.</p>
  
  <p>i want get the value to update from an another table which has a foreignkey.</p>
  
  <pre><code>Table Table1:
  
  Id Primary
  UserNumber INT
  
  
  Table Table2
  Id Primary
  Id_T1 ForeignKey
  UserId INT
  OrderNumber INT
  </code></pre>
  
  <p>can someone help with this pls?</p>
  
  <p>Note: In the Table T2 i need the UserId Value Where the ordernumber has the maximum value.</p>
  
  <p>for example: </p>
  
  <pre><code>Id_T1 UserId OrderNumber 
  15    24        1 
  15    55        2 
  15    72        3
  </code></pre>
  
  <p>the value i want to receive is the 72.</p>
  
  <pre><code>i have try this:
  update T1 set T1.UserNumber = T2.UserId
  FROM Table1 AS T1
  INNER JOIN Table2 AS T2
  ON T1.Id = T2.IdMain
  WHERE T1.Id = T2.IdMain
  AND T2.OrderNumber = (SELECT MAX(T2.OrderNumber) FROM Table2)
  </code></pre>
  
  <p><strong>but i get this error:</strong></p>
  
  <blockquote>
    <p>Msg 147, Level 15, State 1, Line 6 An aggregate may not appear in the
    WHERE clause unless it is in a subquery contained in a HAVING clause
    or a select list, and the column being aggregated is an outer
    reference.</p>
  </blockquote>