Update Sql from a sql query

Other Tech Topics
1

I have a customer table that I need to update with the spending of the previous month. The query should sum tbinvoice.numTotal for each client and that value should be used to update the client table.

Assuming last month was November, this is as far as I have been able to go:

UPDATE tbCustomer SET tbCustomer.numCurrentSpend = (SELECT SUM (tbInvoice.numTotal) as NumT FROM tbInvoice WHERE ((tbInvoice.lngMonth)=10 AND tbInvoice.numID =tbCustomer.numID))

FROM tbCustomer;

This produces a missing operator message.

Thanks for any assistance.

2 
WITH UltSums
AS
(
	SELECT numID, SUM(numTotal) AS numTotal
	FROM tbInvoice
	WHERE lngMonth = MONTH(CURRENT_TIMESTAMP) -1
)
UPDATE C
SET numCurrentSpend = ISNULL(U.numTotal, 0)
FROM tbCustomer C
	LEFT JOIN UltSums U
		ON C.numID = U.numID;
3

Hi Ifor
Thanks for the quick response. I ran the query in SQL MMS and an error:
Column 'tbInvoice.numID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

4

Add GROUP BY numID under the WHERE clause.

5

Ifor thank you, the query is perfect. Very much appreciated.