PostgreSQL:使用聚合函数更新 [英] PostgreSQL: UPDATE using aggregate function

问题描述

我想更新表 paneldata 设置列 ibase 使用聚合函数.

I want to update the table paneldata setting the column ibase using an aggregate function.

UPDATE paneldata p
SET ibase=SUM(1/i.dist)
FROM ibaselang i
WHERE p.gid=i.gid
AND i.instp<p.period

这会导致 ERROR: UPDATE 中不允许使用聚合函数

CREATE TABLE public.ibaselang
(
  gid integer,
  dist double precision,
  buildid integer,
  instp smallint
)
WITH (
  OIDS=FALSE
);

解决方法

不幸的是，我不知道如何在子查询中实现我的 WHERE 函数.

推荐答案

这里有一个通用的示例，说明如何操作.

Here's a generic example of how to do it.

UPDATE public.customer_value cv
SET total_value = sub_q.sum_val 
FROM 
    (
    SELECT SUM(order_amount) AS sum_val, o.customer_id 
    FROM public.orders AS o
    GROUP BY o.customer_id
    ) AS sub_q
WHERE sub_q.customer_id = cv.customer_id;

如果你想完整地尝试这个例子，你可以像这样创建虚拟数据:

If you want to try this example out in full you can create the dummy data like this:

CREATE TABLE public.customer_value
(
  customer_id int 
, total_value numeric (10,2)
);

CREATE TABLE public.orders 
(
  customer_id int
, order_amount numeric(10,2)
);

INSERT INTO public.customer_value
(customer_id)
VALUES 
  (1)
, (2);


INSERT INTO public.orders
(customer_id, order_amount)
VALUES 
 (1, 10)
,(1, 10)
,(2, 7.5)
,(2, 7.5);

这篇关于PostgreSQL:使用聚合函数更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！