SQL - converting a SELECT to and UPDATE

yoda

yoda

yoda

Associate
Joined
18 Oct 2002
Posts
858
Location
Cheshire
I have the following SQL SELECT statement...

Code: 
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

Code: 
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
 
in theory, can I not just do something like....

Code: 
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?
 
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?
 
Code: 
update tblSale
SET ContractStatusID = 4, DDPaid = True
Where [contact number] in (
SELECT distinct tblSale.[contract number - txt]
FROM tblCustomer INNER JOIN (tblSale LEFT JOIN dbo_Payments ON tblSale.[contract number - txt] = dbo_Payments.CustomerCode) ON tblCustomer.CustomerID = tblSale.CustomerID
HAVING (((tblSale.ContractStatusID)=5) AND ((dbo_Payments.Date)<Date()-"5" AND Is not Null) AND ((Sum(dbo_Payments.Amount))>0)));

The above is how I usually change my select statements into update statements in oracle.

I took out the group by section of your SQL added a distinct and only selected one value (usually a primary key)
 
Last edited:
yoda said:
it's coming back with syntax error.... although, I can't see it myself....
Click to expand...
a Cobol programmer once told me "if you are getting a sytnax or logic error. Delete the offending item and it usually fixes the problem."

It actually had surprising results, and helped me realise why the company I work for is in the situation it claims to be in.

TBH with all coding its hard to write it for someone else and have it 100% first time. Just fiddle with it a bit and see what's what.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom