Post reply

Comparing fields that have null values.

  • October 1, 2002 at 12:54 am

    #105173

    Thanks in advance for looking at this.

    I have two tables, A and A_temp that have many common fields. I need to compare A_temp to A. If they are different, I need to set a flag in A and update all the fields in A. If they are the same I do not set the flag. My query works unless the field in A has a null value. There are about 15 fields that are compared and updated. A sampling of the code follows;

    UPDATE a SET

    a.updated = getdate(),

    a.company = a_temp.company,

    a.fname = a_temp.fname,

    a.lname = a_temp.lname,

    a.phone = a_temp.phone

    FROM a INNER JOIN a_temp

    ON a.ID = a_temp.ID

    AND (a.company <> a_temp.company

    OR a.fname <> a_temp.fname

    OR a.lname <> a_temp.lname

    OR a.phone <> a_temp.phone)

    Again, the problem is if a.??? is null. If it has any value the comparison works correctly.

    Thanks, Bill

  • October 1, 2002 at 2:33 am

    #437533

    Use COALESCE or ISNULL, like this:

    COALESCE(a.company,0)

    or

    ISNULL(a.company,0)

    This way, if the value in the column is NULL then 0 will be used for the comparison.

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

    http://www.sql.nu

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • October 1, 2002 at 2:45 am

    #437536

    You could try something like this :

    UPDATE a SET

    a.updated = getdate(),

    a.company = COALESCE(a_temp.company,a.company),

    a.fname = COALESCE(a_temp.fname,a.fname),

    a.lname = COALESCE(a_temp.lname,a.lname),

    a.phone = COALESCE(a_temp.phone,a.lname),

  • October 1, 2002 at 3:09 am

    #437538

    Although I prefer using COALESCE as it is far more explicit when reading the sql you can also SET ANSI_NULLS OFF which and the system will happily compare Nulls.

    cheers,

    Mike

  • October 1, 2002 at 7:35 am

    #437555

    Comparing NULL always present problems. Think you might try something like this. Of course you will need to determine what you would like to set the "a" fields to when a_temp fields are null. Think some of the other replies had some good examples:

    UPDATE a SET

    a.updated = getdate(),

    a.company = a_temp.company,

    a.fname = a_temp.fname,

    a.lname = a_temp.lname,

    a.phone = a_temp.phone

    FROM a INNER JOIN a_temp

    ON a.ID = a_temp.ID

    AND (a.company <> a_temp.company

    or (a.company is null and a_temp.company is not null)

    OR a.fname <> a_temp.fname

    or (a.fname is null and a_temp.fname is not null)

    OR a.lname <> a_temp.lname

    or (a.lname is null and a_temp.lname is not null)

    OR a.phone <> a_temp.phone

    or a.phone is null and a_temp.lname is not null)

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • October 1, 2002 at 10:55 am

    #437575

    THANKS EVERYONE!!!

    It was nice to have different options to choose from and pick the one that worked best for my situation.

    Bill

Viewing 6 posts - 1 through 5 (of 5 total)

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