title: How to update multiple rows in a table using joins and aggregate functions in a single query
body: <p>I have a table that stores statistics about the number of records added to a different table in the same database over a period in time.</p>
  
  <p>This is a simplified schema of the stats table.</p>
  
  <pre><code>+------+-------------+---------+-------+--------+
  |  id  |  programId  |  start  |  end  | count  |
  +------+-------------+---------+-------+--------+
  </code></pre>
  
  <p>IRL the table contains multiple counts, and dates that are computed using a moderately complex query.  Each <code>programId</code> will exist in the table with an <code>end</code> value of <code>null</code>, and that is where the stats are stored for the given program.  A cron job runs to periodically close the time slice (i.e. set the <code>end</code> column to a valid date).  The next time the stats are updated new program rows are created with a <code>start</code> value, and <code>end</code> set to null.</p>
  
  <p>This is a simplified schema of the message table, it contains the data that gets summarized in the stats table. This table holds the data that gets summarized in the stats table.</p>
  
  <pre><code>+------+-------------+------------+--------+
  |  id  |  messageId  | programId  |  time  |
  +------+-------------+------------+--------+
  </code></pre>
  
  <p>I'd like to update all of the program message counts with a single query.  The following query doesn't work because the aggregate function <code>max</code> isn't allowed in the where statement.  Also, I've read warnings about using the <code>coalesce</code> function in the where clause.  Also, it just seems <strong>wrong</strong> to have the same two tables joined over and over again in a single query.  I'm no sql expert (obviously), but this query raises all kinds of red flags with me - even if it worked, I'd be posting here looking for ways to improve it.</p>
  
  <pre><code>UPDATE stats a
  INNER JOIN messages b on a.programId = b.programId
  SET a.numberReceived = 
      ( SELECT COUNT(c.id) 
        FROM messages c LEFT JOIN stats d ON c.programId = d.programId 
        WHERE c.datetime &gt;= coalesce(max(d.end), '1970-01-01 00:00:01)
      )
  WHERE end IS NULL
  </code></pre>
  
  <p>The logic is to only count the messages that have arrived after the last closed time slice if one exists, otherwise count all the messages for the given program (i.e. if this is the first time the program appears in the stats table).</p>
  
  <p>I tried adding this counting logic to a trigger, however, there are more stats being computed than this (which is a simplified example) and many rows are inserted into the messages table at a time.  The upshot was that including this logic in an <code>after insert</code> trigger caused transaction errors, which led to the insert failing.</p>
  
  <p>I know I could do this programmatically by looping through the programs and issuing many sql statements against the DB, but think this can be done in a single statement.</p>