带有左连接和分组依据的 MySQL 更新查询

2021-12-27 00:00:00 group-by 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

它创建了一个三元组 Index1、Index2、EventCounts 的列表.它仅在 t.SpecialEventCount 为 NULL 的情况下执行此操作.我尝试编写的更新查询应该将此 SpecialEventCount 设置为该计数，即上面查询中的 COUNT(m.EventType).这个数字可以是 0 或任何正数(因此是左连接).Index1 和 Index2 在 Table t 中是唯一的，用于标识 MEvent 中的事件.

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.

如何将选择查询修改为更新查询?IE.类似的东西

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.

推荐答案

我认为 (Index1, Index2) 是 Table 上的唯一键，否则我会预计对 t.SpecialEventCount 的引用会导致错误.

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.

编辑查询以使用子查询，因为它无法使用 GROUP BY

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

相关文章