Comparing fields that have null values.

Question

Post reply

Comparing fields that have null values.

billmais

SSC Enthusiast

Points: 114
More actions
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

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

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

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/

J.Mac Mr or Mrs. 500 Points: 508 More actions · Answer 2

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),

Mike2265 Right there with Babe Points: 753 More actions · Answer 3

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

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

billmais SSC Enthusiast Points: 114 More actions · Answer 5

THANKS EVERYONE!!!

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

Bill