SQL WHERE EXISTS is masking errors in Subquery

Question

I am running a query to update a flag in a table that relies on checking that values exist in a subquery, but when the subquery gets an error (divide by zero), the EXISTS statement just sees it as a returned row and carries on with the update.

e.g.

Update xxxx
Set Flagfield=1 
FROM xxxx
WHERE
EXISTS (
Select * FROM yyyy Inner join xxxx on xxx.ID = yyyy.id
WHERE yyyy.int1 / yyyy.int2 > 1)

Has anyone else experienced this behaviour and can it be anticipated?

Answer 1

If I understand your question correctly, then you can try the below code, just add the check for int2 in the where statement.

Update xxxx
Set Flagfield=1 
FROM xxxx
WHERE
EXISTS (
Select * FROM yyyy Inner join xxxx on xxx.ID = yyyy.id
WHERE (yyyy.int2 IS NOT NULL AND yyyy.int2 <> 0) AND (yyyy.int1 / yyyy.int2 > 1))