I have an update statement which has division operation and one row throws exception
Divide by zero exception
and I see that none of the rows got updated .
How to continue on exception so that rows which doesn’t encounter 0 gets updated and row which has 0 fails? Its just a normal update statement without use of any transaction.
Advertisement
Answer
You would filter out rows where the denominator is zero. So, if your query is:
update t set ratio = numerator / denominator;
You would change it to:
update t set ratio = numerator / denominator where denominator <> 0;
Note that sometimes SQL Server evaluates the division for all rows despite the WHERE
clause. So, this is actually safer:
update t set ratio = numerator / nullif(denominator, 0) where denominator <> 0;