SQL Update with a Group by Statement not working

Question

I've got two temp tables.

In table @due_cte I have a series of Customer_Ids, followed by payment amounts collected throughout various points in the code.

The @work table has a lot of various columns including hist_amt_due.

What I want to do is update @work with the amt_due from @due_cte but group/sum all of them by customer_ID

My original code:

update  @work
set     hist_amt_due = sum(isnull(due_amt,0)
from    @work w
join    @due_cte d on w.pmt_customer_no = d.pmt_customer_no
group by  w.pmt_customer_no 

followed by error messages:

Incorrect syntax near the keyword 'group'.

After some digging i found that i should use an inner join so i attempted this, but this too is still not working.

update  @work
set     hist_amt_due = isnull(d.due_amt,0)
from    @work w
inner join  (SELECT pmt_customer_no, sum(isnull(due_amt,0)) from @due_cte group by pmt_customer_no) AS d on w.pmt_customer_no = d.pmt_customer_no

Error Message:

No column name was specified for column 2 of 'd'. 

and

Invalid column due_amount

I am at a loss about how to imperilment this.

sample data:

pmt_customer_no  |  due_amt
1                   50
2                   30
3                   0
4                   30
2                   10
1                   20
5                   80

@work should be updated - where customer number is 1 the due_amout should be: 70, where it's 2 then due_amount should be 40, etc.

Answer 1

Try this:

update w set hist_amt_due = d.s
from @work w
join (select pmt_customer_no, sum(isnull(due_amt, 0)) s 
      from @due_cte group by pmt_customer_no)d on w.pmt_customer_no = d.pmt_customer_no