循环选择和更新PostgreSQL

Question

我找不到错误。我需要通过删除重复项，根据每个项目的最低日期更新表格。

FOR temprow IN
SELECT MIN(orcitem_dtime_inclusao), orcitem_orc_id FROM orcamento_itens GROUP BY orcitem_orc_id ORDER BY orcitem_orc_id ASC
LOOP
UPDATE orcamentos SET orc_dtime_orcamento = temprow.orcitem_dtime_inclusao WHERE orc_id = temprow.orcitem_orc_id;
END LOOP;

Answer 1

您似乎想要：

UPDATE orcamentos o
SET orc_dtime_orcamento = oi.min_orcitem_dtime_inclusao
FROM (SELECT orcitem_orc_id,
MIN(orcitem_dtime_inclusao) as min_orcitem_dtime_inclusao
FROM orcamento_itens
GROUP BY orcitem_orc_id
) oi
WHERE oi.orcitem_orc_id = o.orc_id;

您不需要游标或其他循环机制。您可以在一个查询中完成所有这些操作。