Problem with Nulls and an UPDATE statement
Posted
by Dave
on Stack Overflow
See other posts from Stack Overflow
or by Dave
Published on 2010-05-27T22:45:31Z
Indexed on
2010/05/27
22:51 UTC
Read the original article
Hit count: 144
UPDATE TableA
SET Value = a.Value * b.AnotherValue
FROM TableA AS a
INNER JOIN TableB AS b
WHERE (Condition is true);
Here is the problem. The Value field for TableA does not allow nulls. If the calculation of a.Value * b.AnotherValue yields a null, an error is thrown. Now the question. Is there any way to tell the UPDATE to ignore the SET phase when the result of the calculation is a null and delete the record rather than updating it. This UPDATE is intended to update hundreds of records at a time but will fail if a single null is encountered. Also, please note that using the ISNULL() function and setting the Value to zero is not acceptable. I would like the record to be dropped if a null is encountered. Many thanks in advance for any help rendered.
© Stack Overflow or respective owner