SQLServer update statement chokes on null value, even with isNull()

Question

Please note that I've changed the names of the tables and fields to make this short and understandable.

I have a query that, boiled down, comes to this:

update destTable 
set destField = ( select top 1 isnull(s.sourceField, '') from sourceTable s 
where <various matches between the destTable table and the s table>
);

(I'm aware of the syntax 'update destTable set destField ... from destTable d, sourceTable s ...' but am not sure how to put the "top 1" into it.)

From this I get the SQLServer 2012 Express result:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'destField', table 'destTable'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

For both tables all of the fields are defined as not-null and default ('').

The "top 1" is important because the sourceTable might have multiple matches for the "where" clauses.

I queried all the rows of sourceTable and found that all of its sourceField values are non-null. Yet I get the result.

The nature of the query is that out of maybe 1000 destTable records the match with sourceTable will yield a match for only 300 rows. The other 700 destTable records won't have a match.

I'm not understanding what SQLServer is doing to me. This query works just fine when I last ran it with MySQL.

Thanks in advance, Jerome.

Answer 1

The problem is that the query is not returning any rows . . . hence producing a NULL value. Move the isNULL() outside the subquery:

update destTable 
set destField =  isnull((select top 1 s.sourceField
                         from sourceTable s 
                         where <various matches between the destTable table and the s table>
                        ), '');

By the way, normally I would advocate coalesce() in this situation, because it is the standard. However, the two behave differently, as described by Aaron Bertrand here. In particular, the first argument appears to be evaluated twice, which is quite expensive when it is a subquery. Isnull(), apparently, does not have this problem.