title: SQL Update Multiple Rows with Count from another Table
body: <p>Suppose I have two tables.  One table, tbl1, is "long" and non-aggregated.  The structure is as follows:</p>
  
  <pre><code>Software_Name:
  Word
  PowerPoint
  PowerPoint
  Excel
  Word
  PowerPoint
  </code></pre>
  
  <p>In a second Table, tbl2, I want to summarize the data from the first table, namely the count of Software.  The second Table will have a structure like:</p>
  
  <pre><code>Software_Name:      Count:
  Word                 2
  PowerPoint           3
  Excel                1
  </code></pre>
  
  <p>I have tried:</p>
  
  <pre><code>update tbl2
  set count = 
  (select count(software_name)
  from tbl1
  group by software_name
  where tbl1.software_name = tbl2.software_name)
  from tbl1
  </code></pre>
  
  <p>I get a result inserted into the proper column, but it is not the proper value.  It is the sum of all values, in this case 5.  I have included the <code>where</code> clause because in my tbl1 I have many more software_names than am interested in counting in tbl2.</p>
  
  <p><strong>UPDATE</strong>
  I am using Teradata Aster for this project.  I have been looking at the Aster documentation for the UPDATE command and came across this:</p>
  
  <pre><code>UPDATE [ ONLY ] table
     SET column = expression [, ...]
     [ FROM fromlist ]
     [ WHERE condition | WHERE CURRENT OF cursor_name ];
  </code></pre>
  
  <p>In reading about the fromlist, I came across this bit of information:</p>
  
  <blockquote>
    <p>Note that the target table must not appear in the fromlist unless you intend a
    self-join (in which case it must appear with an alias in the fromlist). </p>
  </blockquote>