带有 SELECT 的 UPDATE 表，但字段为 SUM(someField) [英] UPDATE table with SELECT from another but with a field being SUM(someField)

问题描述

基本上我有这样的事情:

Basically I have something like this:

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id

问题是我想更新 col1 选择如下:

The problem is that I would like to update col1 with the select being like:

SELECT SUM(col1) FROM other_table WHERE Table.id = other_table.id AND period > 2011

编辑

正确答案:

UPDATE bestall  
INNER JOIN (SELECT bestid,SUM(view) as v,SUM(rawView) as rv 
                           FROM beststat 
                           WHERE period > 2011 GROUP BY bestid) as t1 
ON bestall.bestid = t1.bestid
SET view = t1.v, rawview = t1.rv

推荐答案

您不能在 set 子句中直接使用聚合.一种解决方法是子查询:

You can't use aggregates directly in a set clause. One way around that is a subquery:

update  your_table as yt
left join 
        (
        select  id
        ,       count(*) as cnt
        from    other_table
        where   period < 4
        group by
                id       
        ) as ot
on      yt.id = ot.id 
set     col1 = coalesce(ot.cnt,0)

SQL Fiddle 示例.

这篇关于带有 SELECT 的 UPDATE 表，但字段为 SUM(someField)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！