title: MySQL Update query with left join and group by
body: <p>I am trying to create an update query and making little progress in getting the right syntax.
  The following query is working:</p>
  
  <pre><code>SELECT t.Index1, t.Index2, COUNT( m.EventType ) 
      FROM Table t
      LEFT JOIN MEvents m ON
          (m.Index1 = t.Index1 AND
           m.Index2 = t.Index2 AND
          (m.EventType =  'A' OR m.EventType =  'B')
      ) 
      WHERE (t.SpecialEventCount IS NULL)
      GROUP BY t.Index1, t.Index2
  </code></pre>
  
  <p>It creates a list of triplets Index1,Index2,EventCounts. 
  It only does this for case where t.SpecialEventCount is NULL. The update query I am trying to write should set this SpecialEventCount to that count, i.e. COUNT(m.EventType) in the query above. This number could be 0 or any positive number (hence the left join). Index1 and Index2 together are unique in Table t and they are used to identify events in MEvent.</p>
  
  <p>How do I have to modify the select query to become an update query? I.e. something like</p>
  
  <pre><code>UPDATE Table SET SpecialEventCount=COUNT(m.EventType).....
  </code></pre>
  
  <p>but I am confused what to put where and have failed with numerous different guesses.</p>