Problem with Nulls and an UPDATE statement

Question

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.

Answer 1

Assuming you mean leave the value unchanged when nulls are encountered by "I would like the record to be dropped if a null is encountered."

UPDATE TableA
SET Value = isnull(a.Value * b.AnotherValue, a.value)
FROM TableA AS a
INNER JOIN TableB AS b
WHERE (Condition is true);

If you actually want to delete the rows and are using SQL 2008 or later, try the merge statement.

MERGE TableA AS target 
USING TableB as source ON (target.ID = Source.ID)
WHEN MATCHED AND TableB.AnotherValue Is Null THEN DELETE
WHEN MATCHED THEN UPDATE SET target.Value = Target.Value * Source.AnotherValue;

Answer 2

You can't delete rows during an update, you would need two statements: a delete followed by an update.

Answer 3

This can be done simply in two statements:

UPDATE TableA
SET Value = a.Value * b.AnotherValue
FROM TableA AS a
INNER JOIN TableB AS b
WHERE (Condition is true) and (a.Value * b.AnotherValue) is not null;

Delete a FROM TableA AS a
INNER JOIN TableB AS b
WHERE (Condition is true) and (a.Value * b.AnotherValue) is null;

This is the simplest solution.

You can start doing more complex things (transactions, temp tables, MERGE, etc.) if the situation is not fully resolved.