An aggregate may not appear in the set list of an UPDATE statement performing instruction in SQL Ser-CodePudding

I'm trying to perform an UPDATE summarizing the values of a table that are in the VAL_APURADO column grouped by the payment plan code in the COD_PLANO_PAGAMENTO column. When performing the statement, SQL Server returns the following error:

An aggregate may not appear in the set list of an UPDATE statement.

I'm trying to do it like this:

UPDATE TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO
SET VAL_TOTAL_APURADO = SUM(VAL_TOTAL_APURADO)
WHERE (
    SELECT SUM(VAL_TOTAL_APURADO) 
    FROM TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO
    WHERE TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO.COD_PLANO_PAGAMENTO = TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO.COD_PLANO_PAGAMENTO
    GROUP BY COD_PLANO_PAGAMENTO
  ) > 5000;

CodePudding user response：

UPDATE TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO
SET    VAL_TOTAL_APURADO = AggregateQ.Sum_TOTAL_APURADO 
FROM TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO
INNER JOIN 
(
    SELECT COD_PLANO_PAGAMENTO,SUM(VAL_TOTAL_APURADO) AS Sum_TOTAL_APURADO  
    FROM   TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO
    GROUP BY COD_PLANO_PAGAMENTO
) AS AggregateQ ON AggregateQ.COD_PLANO_PAGAMENTO = TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO.COD_PLANO_PAGAMENTO AND AggregateQ.Sum_TOTAL_APURADO > 5000

CodePudding user response：

Your current query makes no sense, as you are taking the SUM from the base table, not from the WHERE subquery. you would need to convert this to a join or APPLY.

You can in any case do this more efficiently and concisely with a window function in an updatable CTE

WITH cte AS (
    SELECT *,
      sum_APURADO = SUM(md.VAL_TOTAL_APURADO) OVER (PARTITION BY md.COD_PLANO_PAGAMENTO)
    FROM TB_MOVIMENTO_PDV_DETALHE_PLANO_PAGAMENTO md
)
UPDATE cte
SET VAL_TOTAL_APURADO = cte.sum_APURADO
WHERE cte.sum_APURADO > 5000;