Why update query is failing with error Incorrect syntax near the keyword 'GROUP'

Question

This query is not working

update p
set p.TotalAmount =  SUM(pay.Amount)
FROM ##tmp t
INNER JOIN SMILAPPLICATION..Purchases p on t.ShoppingCartPaymentID = p.ExternalPurchaseId
INNER JOIN SMILAPPLICATION..PurchaseItems pit ON pit.ExternalPurchaseItemId = t.ShoppingCartID
INNER JOIN SMILAPPLICATION..Sessions s on t.SessionId = s.SessionId
LEFT JOIN SMILAPPLICATION..SessionPricing sp on s.SessionId = sp.SessionId
INNER JOIN SMILAPPLICATION..Payments pay ON pay.PurchaseItemId = pit.PurchaseItemId
GROUP By p.PurchaseId

Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'GROUP'.

While this is working

update y
set y.TotalAmount =  x.Total
FROM
(
    select p.PurchaseId, SUM(pay.Amount) AS Total
    FROM ##tmp t
    INNER JOIN SMILAPPLICATION..Purchases p on t.ShoppingCartPaymentID = p.ExternalPurchaseId
    INNER JOIN SMILAPPLICATION..PurchaseItems pit ON pit.ExternalPurchaseItemId = t.ShoppingCartID
    INNER JOIN SMILAPPLICATION..Sessions s on t.SessionId = s.SessionId
    LEFT JOIN SMILAPPLICATION..SessionPricing sp on s.SessionId = sp.SessionId
    INNER JOIN SMILAPPLICATION..Payments pay ON pay.PurchaseItemId = pit.PurchaseItemId
    GROUP By p.PurchaseId
) x inner join SMILAPPLICATION..Purchases y on x.PurchaseId = y.PurchaseId 

WHY?

Answer 1

I think this is related to the way UPDATE statement is working behind the scenes and it will not allow any aggregation function:

1) You go to the base table. It cannot have an alias because an alias would create a working table that would be updated and then disappear after the statement is finished, thus doing nothing.

2) You go to the WHERE clause. All rows (if any!) that test TRUE are marked as a subset. If there is no WHERE clause, then the entire table is marked. The name of this set/pseudo-table is OLD in Standard SQL.

3) You go to the SET clause and construct a set/pseudo-table called NEW. The rows in this table are build by copying values from the columns are not mentioned from the original row to the NEW row. The columns are assigned all at once. That is, the unit of work is a row, not one column at a time.

4) The OLD subset is deleted and the NEW set is inserted.

I guess that, theoretically, SUM(...) with GROUP BY could be put in a pseudo table and copy the data, but I think UPDATE is meant to work (fast) for direct copy of data.

Answer 2

The GROUP BY is not supported in the UPDATE statement. See MSDN for the complete syntax of the statement.

And it is supported in the <table_source> (FROM)