title: Update column based on aggregate function with sub query
body: <p>I need to have a column with data taken from a count() function with a subquery. I am working with a temporary table at the moment and it is created by: </p>
  
  <pre><code>CREATE TABLE tempfact1 AS
    (
      SELECT coursesloc.courseID, preferences.appid, campuses.campusid
      FROM coursesloc, preferences, campuses
    WHERE coursesloc.courseID = preferences.courseID AND coursesloc.campusid = campuses.campusid AND preferences.prefaccepted = 'Y'
    )
  ;
  
  ALTER TABLE tempfact1
  ADD TNA NUMERIC;
  </code></pre>
  
  <p>Then I run the following to try and generate the TNA (total number of applicants per campus) with the following:</p>
  
  <pre><code>UPDATE tempfact1
  SET TNA = (SELECT COUNT(appid) FROM tempfact1 GROUP BY campusid)
  </code></pre>
  
  <p>Which I cannot do because it is returning more than one row. I'm unsure how to get it to only output one row at a time in the UPDATE, or if there is an easier and better way to just do this in the CREATE TABLE command. </p>