title: How can I update a table by using subquery
body: <p>I have got table which is  "Stocks". There are thousands of records inside of this table and I need to UPDATE it acc. to following Example. (I use SQL Server 2008)</p>
  
  <pre><code>STOCKS.stock_name     STOCKS.a_id
  A.001                  0
  B.001                  0
  C.001                 20
  A.002                  0
  B.002                 10
  A.003                  0
  </code></pre>
  
  <p>After executing the code, I want to have a table which is like:</p>
  
  <pre><code>STOCKS.stock_name     STOCKS.a_id
  A.001                 20
  B.001                 20
  C.001                 20
  A.002                 10
  B.002                 10
  A.003                  0
  </code></pre>
  
  <p>What needs to happen here is that the SQL understands 001 - 002 and 003 are 3 different groups. (I'm thinking about using SUBSTRING(stock_name,3,20) since the first 2 part of my text value will not be like "<strong>AA.002</strong>". Grouping will start from the third part of each stock name.) It selects the maximum <strong>a.id</strong> value and update the rows accordingly. I really need your help about this issue. </p>
  
  <p>Many thanks in advance!</p>