SQLServer update statement clamps a null value, even with isNull ()

Question

Note that I have changed the names of the tables and fields to make this short and clear.

I have a query that, when welded, comes to the following:

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 know about the syntax "update destTable set destField ... from destTable d, sourceTable s ...", but I'm not sure how to put "top 1" in it.)

From this, I get the result of SQLServer 2012 Express:

 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 fields are defined as non-null and default ('').

Top 1 is important because sourceTable may have multiple matches for where clauses.

I requested all sourceTable strings and found that all of its sourceField values ​​are non-zero. But I get the result.

The nature of the query is that out of 1000 destTable entries, a match with sourceTable will only match 300 rows. The other 700 destTable entries will not match.

I do not understand what SQL Server does for me. This query works fine the last time I ran it with MySQL.

Thanks in advance, Jerome.

Answer 1

The problem is that the query does not return any rows., Therefore, creating a NULL value. Move 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, usually I would defend coalesce() in this situation, because it is a standard. However, they behave differently, as described here by Aaron Bertrand here . In particular, the first argument seems to be evaluated twice, which is quite expensive when it is a subquery. isNULL() does not seem to have this problem.