Post reply

"Update" and "Not in" keywords

  • August 3, 2005 at 1:58 am

    #165110

    Hi,

    My SQL statement is ...

    update supplier set Del_Status='YES' where Supp_ID='S01' and

    supp_ID not in

    (select supp_ID from Items_Master )

    The above statement does not update any record in the supplier table although 'S01' does not exist at Items_Master table..

    But, I've found a new way like this..

    update supplier set Del_Status='YES' where Supp_ID='S01' and

    supp_ID not in

    (select supp_ID from Items_Master where isnull(supp_ID,'NULL')<>'NULL')

    And it works fine. "Supp_ID" field of Items_Master may contains "Null" value.

    I would like to know that if there is any other way to solve out bcoz there are alot of tables like this in my project and I don't want to follow the above way that makes extra work.

  • August 3, 2005 at 12:49 pm

    #579316

    Instead of using NOT IN, try using a left join like this,

    update sup 
set sup.Del_Status='YES' 
from supplier sup
  left join Items_Master mas
  on sup.supp_ID = mas.supp_ID
where sup.Supp_ID='S01' 
  and mas.supp_ID IS NULL

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply