mysql 连接查询分组,MySQL更新查询，左连接和分组依据

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.

解决方案

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