How to update a column using inner join and aggregate function?-CodePudding

I'm trying to update the subtotal of all orders in my Orders table by joining my Cart table and taking the sum of all the items in the cart. Here's what I have:

UPDATE O 
SET O.subtotal = ISNULL(SUM((C.price - C.discount_price) * C.qty), 0)
FROM Orders AS O
INNER JOIN Cart AS C ON O.cart_id = C.cart_id 
WHERE O.date > '01/01/2021'

However I get an error

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

How can I make the above statement work?

CodePudding user response：

In UPDATE statement you can not use aggregate functions that: SUM, GROUP BY

For correct UPDATE:

UPDATE  Orders   
SET 
    subtotal = C.sum_total 
FROM 
    Orders AS O
INNER JOIN (
    select 
        cart_id,  
        ISNULL(SUM((price - discount_price) * qty), 0) as sum_total 
    from Cart 
    group by 
        cart_id
) AS C ON O.cart_id = C.cart_id
WHERE O.date > '01/01/2021'