SQL - converting a SELECT to and UPDATE

[yoda]

I have the following SQL SELECT statement...

SELECT tblSale.[contract number - txt], Sum(dbo_Payments.Amount) AS SumOfAmount
FROM tblCustomer INNER JOIN (tblSale LEFT JOIN dbo_Payments ON tblSale.[contract number - txt] = dbo_Payments.CustomerCode) ON tblCustomer.CustomerID = tblSale.CustomerID
GROUP BY tblSale.[contract number - txt], tblSale.ContractStatusID, tblSale.DDPaid, tblSale.[DDPaid date], dbo_Payments.Date
HAVING (((tblSale.ContractStatusID)=5) AND ((dbo_Payments.Date)<Date()-"5" AND Is not Null) AND ((Sum(dbo_Payments.Amount))>0));

however, I want it to update the following

SET tblSale.ContractStatusID = 4, tblSale.DDPaid = True

But I need to keep the grouping in the select statement.
I can't figure out the best way of doing it...

Can someone help?

Thanks

 

[yoda]

in theory, can I not just do something like....

UPDATE tblCustomer INNER JOIN (tblSale LEFT JOIN dbo_Payments ON tblSale.[contract number - txt] = dbo_Payments.CustomerCode) ON tblCustomer.CustomerID = tblSale.CustomerID SET tblSale.ContractStatusID = 4, tblSale.DDPaid = True
WHERE tblSale.[contract number - txt] = (SELECT tblSale.[contract number - txt], Sum(dbo_Payments.Amount) AS SumOfAmount
   FROM tblCustomer INNER JOIN (tblSale LEFT JOIN dbo_Payments ON tblSale.[contract number - txt] = dbo_Payments.CustomerCode) ON tblCustomer.CustomerID = tblSale.CustomerID
   GROUP BY tblSale.[contract number - txt], tblSale.ContractStatusID, tblSale.DDPaid, tblSale.[DDPaid date], dbo_Payments.Date
   HAVING (((tblSale.ContractStatusID)=5) AND ((dbo_Payments.Date)<Date()-"5" AND Is not Null) AND ((Sum(dbo_Payments.Amount))>0)));

Can I do it like that?

 

[yoda]

I've got a pre-customer ms access database.... and a customer database (called Ebillz) that uses SQL server.... - hence two databses....
the problem is, the status fields in the MS access database have to be manually updated whne the 'pre-customer' becomes live and when they pay.... etc etc etc etc

Does that help?

 

[yoda]

it's coming back with syntax error.... although, I can't see it myself....