#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2010
    Posts
    2
    Rep Power
    0

    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 -

    update ACCDEBTORS
    set ACCDEBTORS.REGISTERNAME = "NOT ACTIVE"
    where ACCOUNTNUMBER IN
    (SELECT ACCOUNTNUMBER
    FROM HEADOFFICEDEBTORS
    WHERE Not ACCDEBTORS.ACCOUNTNUMBER = HEADOFFICEDEBTORS.ACCOUNTNUMBER)

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Try something like:

    SQL Code:
    UPDATE ACCDEBTORS A
    SET A.REGISTERNAME = "NOT ACTIVE"
    WHERE NOT EXISTS
    (
    SELECT * FROM HEADOFFICEDEBTORS H
      WHERE (H.ACCOUNTNUMBER = A.ACCOUNTNUMBER)
    )
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2010
    Posts
    2
    Rep Power
    0
    Clivew

    Thankyou very much. I have a lot of learning ahead of me.

    Regards



    Originally Posted by clivew
    Try something like:

    SQL Code:
    UPDATE ACCDEBTORS A
    SET A.REGISTERNAME = "NOT ACTIVE"
    WHERE NOT EXISTS
    (
    SELECT * FROM HEADOFFICEDEBTORS H
      WHERE (H.ACCOUNTNUMBER = A.ACCOUNTNUMBER)
    )
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2010
    Posts
    52
    Rep Power
    4
    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.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8

    Thumbs up


    Yes, ' not exists' can be used in delete statements. Here is a example :

    delete from banci b
    where not exists
    (select *
    from conturibancare cb
    where (cb.bancaid = b.cod)
    )

IMN logo majestic logo threadwatch logo seochat tools logo