Update with join - Problem with null in where clause

  • update a

    set a.comments1a = b.Subdivision

    From Notes a

    inner join CowettaCoSubs b

    on a.account_id = b.account_id

    where a.comments1 is null

    This "update" updates nothing

    If I run the above w/o the where, it of course updates all records.

    What is my problem within the Null logic or where clause.

    What I wish to populate is the comments1a field where there are null values in the comments1 field.

    Thanks,

    Dobe

  • there must not be any null values for a.comments1

  • also not sure if it matters but u dont need the alias in front of the feild ur updating as long as u specify which table ur updating after the update keyword

    update a

    set comments1a = b.Subdivision --removed the a. from comments1a

    From Notes a

    inner join CowettaCoSubs b

    on a.account_id = b.account_id

    where a.comments1 is null

    how many records are returned when u run this? I would guess zero.

    select *

    From Notes a

    inner join CowettaCoSubs b

    on a.account_id = b.account_id

    where a.comments1 is null

  • u should also have the check to see if u dont need to update

    update a

    set comments1a = b.Subdivision

    From Notes a

    inner join CowettaCoSubs b

    on a.account_id = b.account_id

    where a.comments1 is null

    and isnull( a.comments1a, '-1') <> isnull( b.Subdivision, '-1') --no sence in updating it if it is already equal. So u check to ensure their not equal.

  • First, I'd like to say I like the Doberman. That is my breed, and I lost my best friend almost 3 years ago. She was 7, loyal, very intelligent, and a true companion.

    Now to answer you questions. Yes, there are null values in Comments1, and I have run the following code, which results in no data sets returned.

    select b.Customer_name, b.subdivision, a.account_id as bAccountId, a.account_id, a.comments1, a.comments1a, a.comments2

    from CowettaCoSubs b inner join notes a

    on a.account_id = b.account_id

    where a.comments1 is null

    If I remove the where clause, the result set shows null as well as the remainder of the data.

    I even tried to eliminate the nulls first via a CTE, same result.

    Dobe

  • The only possible reason I can think for this is that you actually have values that are 'NULL' as character value rather than being NULL in the database sense. Do you get the rows returned when you change it to where a.comments1 ='NULL' OR a.comments1 like '%NULL%'?

  • That was it. The nulls are not null values, but 'Null'... I didn't see that one comming. I imported this from Access, mabe that is where the problem originated from there, it had been imported from Excel.

    Thanks again,

    Dobe

  • Haven't you looked to see what you're updating?

    select a.comments1a, b.Subdivision

    From Notes a

    inner join CowettaCoSubs b

    on a.account_id = b.account_id

    where a.comments1 is null

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris Morris-439714 (3/4/2010)


    Haven't you looked to see what you're updating?

    Apparently not, but thanks for the advise.

Viewing 9 posts - 1 through 8 (of 8 total)

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