SQL Update from another tab

Question

Hello, I’m trying to update the product table using the average unit values of one year in the document table (where the notes enter) and the q instruction I’m using returns with error:

UPDATE DimProduto
SET PM = AVG(DimDocumento.Vu)
FROM DimProduto
INNER JOIN
DimDocumento
ON
DimProduto.ProdId = DimDocumento.ProdId
WHERE (DimDocumento.DtCadstr BETWEEN CONVERT(VARCHAR(10), DateAdd(mm, -12,GetDate()-1),3) AND CONVERT (VARCHAR(10),GETDATE()-1,3))

the mistake is:

Mensagem 157, Nível 15, Estado 1, Linha 175
Uma agregação pode não aparecer na lista de conjuntos de uma instrução UPDATE.

PM is the average price field of the Dimproduto table and Vu is the field with the unit value of the Dimdocumento table. I need to filter the period in a year to have more updated values in the comparisons. I appreciate any help.

Answer 1

Hello,

You can use an OUTER APPLY to solve this without using subquery

        UPDATE A
        SET pm = ISNULL(C.pm, 0)
        FROM dimproduto as A
        OUTER APPLY (
                    SELECT AVG(vu) AS pm
                    FROM dimdocumento  AS B
                    WHERE A.prodid = B.prodid
                    ) AS C

        SELECT * 
        FROM dimproduto

Answer 2

With aggregation function will not achieve even... I suggest using sub-select with exists.

Example:

UPDATE dimproduto
   SET pm = (SELECT AVG(dimdocumento.vu)
                FROM dimdocumento
               WHERE dimproduto.prodid = dimdocumento.prodid
                 AND dimdocumento.dtcadstr BETWEEN
                     convert(VARCHAR(10), dateadd(mm, -12, getdate() - 1), 3) AND
                     convert(VARCHAR(10), getdate() - 1, 3))
 WHERE EXISTS (SELECT 1
                 FROM dimdocumento
                WHERE dimproduto.prodid = dimdocumento.prodid
                  AND dimdocumento.dtcadstr BETWEEN
                     convert(VARCHAR(10), dateadd(mm, -12, getdate() - 1), 3) AND
                     convert(VARCHAR(10), getdate() - 1, 3))

See spinning here.