Sum sale amount from one table and update the total to another table

Question

Using SQL Server Compact Edition (2008 R2)

1. Tables (Customer and Orders)
2. PK (Customer : SID, Orders : Customer_SID)

I want to sum the Orders.Sales_Amount and write the totals to Customer.Sales_Total based on the SIDs.

I must be using the inner join statement incorrectly as I get an error in the FROM statement.

UPDATE customer
SET sales_total = aggr.sales_total
FROM customer
INNER JOIN (
    SELECT sid
        ,sum(sales_amount) sales_total
    FROM customer
    INNER JOIN orders
        ON (customer.sid = orders.customer_Sid)
    GROUP BY customer.sid
    ) aggr
    ON customer.sid = aggr.sid;

Answer 1

There is a simpler way of accomplishing the update you are after:

UPDATE customer
SET sales_total = (SELECT SUM(sales_amount)
                   FROM orders
                   WHERE orders.customer_Sid = customer.sid)

Demo here