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)
