MySQL Update query with left join and group by

Question

I am trying to create an update query and making little progress in getting the right syntax. The following query is working:

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 

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.

How do I have to modify the select query to become an update query? I.e. something like

UPDATE Table SET SpecialEventCount=COUNT(m.EventType)..... 

but I am confused what to put where and have failed with numerous different guesses.

Answer 1

I take it that (Index1, Index2) is a unique key on Table, otherwise I would expect the reference to t.SpecialEventCount to result in an error.

Edited query to use subquery as it didn't work using GROUP BY

UPDATE     Table AS t     LEFT JOIN (         SELECT             Index1,             Index2,             COUNT(EventType) AS NumEvents         FROM             MEvents         WHERE             EventType = 'A' OR EventType = 'B'         GROUP BY             Index1,             Index2     ) AS m ON         m.Index1 = t.Index1 AND         m.Index2 = t.Index2 SET     t.SpecialEventCount = m.NumEvents WHERE     t.SpecialEventCount IS NULL 

Answer 2

Doing a left join with a subquery will generate a giant temporary table in-memory that will have no indexes.

For updates, try avoiding joins and using correlated subqueries instead:

UPDATE     Table AS t SET     t.SpecialEventCount = (         SELECT COUNT(m.EventType)         FROM MEvents m         WHERE m.EventType in ('A','B')           AND m.Index1 = t.Index1           AND m.Index2 = t.Index2     ) WHERE     t.SpecialEventCount IS NULL 

Do some profiling, but this can be significantly faster in some cases.