从子查询更新多列

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