postgresql - 使用从联接派生的聚合值更新所有使用别名

Question

SELECT activities.id, max(symbols.bought_at) AS bought_at 
FROM "activities" 
  JOIN holdings ON trackable_id = holdings.id AND trackable_type = 'Holding'   
  JOIN symbols on symbols.holding_id = holdings.id 
GROUP BY activities.id"

我有一个看起来像上面的 SQL。这工作正常。但是，我想将所有活动的 created_at 更新为别名 buy_at。我收到一个错误，说买了_at 不是一列。在 Postgres 中可以这样做吗？

标签： postgresql

Answer 1

您可以将该查询用作 UPDATE 语句的源：

update activities 
   set created_at = t.bought_at
from (
  SELECT activities.id, max(symbols.bought_at) AS bought_at 
  FROM activities
    JOIN holdings ON trackable_id = holdings.id AND trackable_type = 'Holding'   
    JOIN symbols on symbols.holding_id = holdings.id 
  GROUP BY activities.id
) t
where activities.id = t.id;

这假定这activities.id是该表的主键。