SQL Update Set: Higlight Customers without Invoices, Update and join Tables?

30 pts.
Tags:
AS/400 DB2
DB2
SQL statement
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

Something like this?

Update Customer Set InActiveFld = ‘INACTIVE’
where CustNo not in
(Select distinct i.ACCOUNT from i where i.invoiceyear>=2008) )

Phil

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • carlosdl
    off-topic: welcome back Phil.
    68,430 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following