从子查询更新多列 [英] Update multiple columns from subquery

问题描述

这种类型的东西已经被问过几次了，但并不是我要找的东西.我需要SET等于子查询不同部分的两行.

This type of thing has been asked a few times before, but is not quite what I am looking for. I need to SET two rows equal to different parts of a subquery.

我当前正在使用:

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语句显然得到了简化...但是基本上它是相同的子查询，但是我不认为我应该运行两次?

The WHERE statements were obviously simplified...but basically its the same subquery but I don't think I should be running it twice?

我想做类似...

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

推荐答案

您可以简单地将表联接到进行一些计算的子查询中，

You can simply join the table in a subquery that do some calculations,

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

这篇关于从子查询更新多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！