SQL Update Set: Higlight Customers without Invoices, Update and join Tables?
Hello,
I am not sure how to do this with an SQL Update Set statemet:
I have a table customers and I have related invoices. I would like to set one field to "not active" in the customer table that did not receive invoices since 2008.
I can "Select" the corresponding records in the customer table without problem:
SELECT 'not active' c.myCustomerField FROM c LEFT OUTER JOIN (Select distinct i.ACCOUNT test from i where i.invoiceyear>=2008) d ON s.ACCOUNT=d.test WHERE d.test is NULL
How do I get the Join into the update statement?
update c (??? Join here???) set c.mycustomerfield = 'not active' where ??????
How can I set myCustomerField?
Software/Hardware used:
as400 db2 sql statement
I am not sure how to do this with an SQL Update Set statemet:
I have a table customers and I have related invoices. I would like to set one field to "not active" in the customer table that did not receive invoices since 2008.
I can "Select" the corresponding records in the customer table without problem:
SELECT 'not active' c.myCustomerField FROM c LEFT OUTER JOIN (Select distinct i.ACCOUNT test from i where i.invoiceyear>=2008) d ON s.ACCOUNT=d.test WHERE d.test is NULL
How do I get the Join into the update statement?
update c (??? Join here???) set c.mycustomerfield = 'not active' where ??????
How can I set myCustomerField?
Software/Hardware used:
as400 db2 sql statement
ASKED:
March 28, 2011 2:03 PM
UPDATED:
March 28, 2011 9:38 PM
Answer Wiki:
Something like this?
Update Customer Set InActiveFld = 'INACTIVE'
where CustNo not in
(Select distinct i.ACCOUNT from i where i.invoiceyear>=2008) )
Phil
To see all answers submitted to the Answer Wiki: View Answer History.
Where does s.ACCOUNT come from? I don’t see s defined though it seems like it must be the same as c. Then again, you wrote “I can “Select” the corresponding records in the customer table without problem”, so something seems to be missing.
Can you clarify?
Tom
off-topic: welcome back Phil.