September 1st, 2010, 07:05 PM
Newbie SQL assistance
I have 2 tables - AccDebtors and HeadOfficeDebtors in the same database.
The HeadOfficeDebtors table contains only 1 field - AccountNumber which contains current active account numbers.
I need to change the AccDebtors.RegisterName to "Not Active" where Accdebtors.AccountNumbers are not matched to the account numbers in HeadOffice Numbers.
I tried -
set ACCDEBTORS.REGISTERNAME = "NOT ACTIVE"
where ACCOUNTNUMBER IN
WHERE Not ACCDEBTORS.ACCOUNTNUMBER = HEADOFFICEDEBTORS.ACCOUNTNUMBER)
September 1st, 2010, 10:08 PM
Try something like:
UPDATE ACCDEBTORS A
SET A.REGISTERNAME = "NOT ACTIVE"
WHERE NOT EXISTS
SELECT * FROM HEADOFFICEDEBTORS H
WHERE (H.ACCOUNTNUMBER = A.ACCOUNTNUMBER)
September 2nd, 2010, 12:38 AM
Thankyou very much. I have a lot of learning ahead of me.
Originally Posted by clivew
September 16th, 2010, 01:14 AM
I didnt know how to do that /that this was possible either, thanks!!
This will shorten a lot of queries I have.
The WHERE NOT EXISTS will work in DELETE statements also right?
Like if i wanted to clean up all the order items that dont have a matching order.
September 17th, 2010, 12:15 AM
Yes, ' not exists' can be used in delete statements. Here is a example :
delete from banci b
where not exists
from conturibancare cb
where (cb.bancaid = b.cod)