In SQL How is it possible to use the Sum Function

问题:

This is the code that i used

UPDATE Customers c   
SET c.TotalP= 
   (SELECT SUM(b.Price) 
    FROM Products b
   WHERE b.CustomerID= c.CustomerID)

I im trying to set a value x to the TotalP field in the Customers table. The value x is equal to the sum of the coressponding prices in the Products table where the Customer ID's are the same.

The above code returns an error of "Operation must be an Updateable query"

回答1:

You can use DSum:

UPDATE Customers   
SET TotalP = DSum("Price","Products","CustomerID = " & CustomerID)

But it does raise the question as to why you are updating a table with a calculated value when the information can be obtained from a query.

回答2:

Aside from VBA scripting, Microsoft has three methods: https://support.microsoft.com/en-us/kb/116142

The following are two SQL statements for updating one record at a time using a cross-join:

UPDATE tmp SET tmp.non_null_cnt = 0;

UPDATE tmp, ESC_Results SET tmp.non_null_cnt = [tmp].[non_null_cnt]+1
WHERE ESC_Results.asset4_id Is Not Null;

It will be slower than the other methods because it has to re-calculate the sum for every row stored.