基本上我有这样的事情:
UPDATE
Table
SET
Table.col1 = other_table.col1,
FROM
Table
INNER JOIN
other_table
ON
Table.id = other_table.id
问题是我想更新col1
选择如下:
SELECT SUM(col1) FROM other_table WHERE Table.id = other_table.id AND period > 2011
Edit
正确答案:
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
您不能直接在 a 中使用聚合set
条款。解决这个问题的一种方法是子查询:
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 中的示例。 http://sqlfiddle.com/#!2/20a33/8
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)