Comparing 2 Columns Containing Null Values

Hi All.

I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)

If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

I'd be grateful for any pointers!

Thanks in advance

Sorted it :)

I've used the ISNULL(exp,'') function, as I'm not bothered about updating nothing ('') to null and vice versa.

Just got to go back and alter all 60 scripts now

Nick

|||

Nick Colebourn wrote:

I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.

In this type of situation you would replace your ISNULL with some impossible calue that would never come up. Here are some other ideas:

1. You only want to update Tab_A with Tab_B's value if they're different. Why not just update it all the time?

Cons: Slower Update, More Locks, More overhead if set to full recovery mode.

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE

2. Make your JOIN even more complicated

UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
(TAB_B.TRADCODE IS NULL AND TAB_A.TRADCODE IS NOT NULL)

OR (TAB_B.TRADCODE IS NOT NULL AND TAB_A.TRADCODE IS NULL)

OR TAB_B.TRADCODE <> TAB_A.TRADCODE
)

|||

Hi Jared,

Thanks for your reply. I think I'm going to take a multi faceted approach to this, some of my tables are only a few thousand rows, so I'll probably just do the full update, and some are 20 million rows or more, so I'll spend the time writing the intricate where clauses or experimenting with the ISNULL function.

Cheers for taking the time to respond.

Nick