10 May 2018

T-SQL Error: An aggregate may not appear in the set list of an update statement

A reporting project contains a table with the product summary, the table has a lot of KPIs and specifically the "Last Sale Date" of a product.
According to the company rules, a product is sold when it is paid.
The following T-SQL code (shown here very simplified) can be used to update that column:
UPDATE Product
SET LastSalesDate = MAX(Orders.PaymentDate)
FROM Product
    INNER JOIN Orders ON (Orders.ProductID = Product.ProductID)

After executing the statement in Sql Server the following error occurs:

Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.


The error indicates that a column of a table is being updated applying an aggregate function directly.
The solution is to rewrite the query, using for example a sub-query.
Example:

UPDATE Product
SET LastSalesDate = LastSaleDates.PaymentDate
FROM Product
    INNER JOIN 
    (
        SELECT MAX(Orders.PaymentDate) AS PaymentDate
            ,Orders.ProductID
        FROM Orders
        GROUP BY Orders.ProductID
    ) LastSaleDates ON (LastSaleDates.ProductID = Product.ProductID)


Another possible solution is to use a Common Table Expression (CTE).
;WITH [LastSaleDates] AS
(
    SELECT MAX(Orders.PaymentDate) AS PaymentDate
            ,Orders.ProductID
    FROM Orders
    GROUP BY Orders.ProductID 
)

UPDATE Product
SET LastSalesDate = LastSaleDates.PaymentDate
FROM Product
    INNER JOIN LastSaleDates ON (LastSaleDates.ProductID = Product.ProductID)

No comments: