本文介绍了MySQL更新查询,左连接和分组依据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试创建更新查询,并且在获取正确的语法方面进展甚微。
以下查询正在工作:

   $ c>,否则我会期望引用 t.SpecialEventCount 导致出错。



编辑的查询使用子查询,因为它无法使用 GROUP BY

  UPDATE 
表格AS t
LEFT JOIN(
SELECT
索引1,
索引2,
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


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

这篇关于MySQL更新查询,左连接和分组依据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-17 02:40
查看更多