mysql - 从子查询更新多列

Question

这种类型的问题之前已经问过几次，但不是我要找的。我需要 SET 两行等于子查询的不同部分。

我目前正在使用:

UPDATE records
SET leads=(SELECT COUNT(*) FROM leads_table WHERE leads_table.blah=records.blah),
earnings=(SELECT SUM(amount) FROM leads_table WHERE leads_table.blah=records.blah)

WHERE 语句显然得到了简化...但基本上它是相同的子查询，但我认为我不应该运行它两次？

我想做类似...

UPDATE records
SET (leads,earnings)=(SELECT COUNT(*),SUM(amount) FROM leads_table WHERE leads_table.blah=records.blah)

Answer 1

您可以简单地在执行一些计算的子查询中加入表，

UPDATE  records a
        INNER JOIN
        (
            SELECT  blah, 
                    COUNT(*) totalCount,
                    SUM(amount) totalSum
            FROM    leads_table
            GROUP   BY blah
        ) b ON  b.blah = a.blah
SET     a.leads = b.totalCount
        a.earnings = b.totalSum