SQL Server How do you get SQL to recognize WHERE column = NULL?

mwecs4sa 于 5个月前发布在其他

关注(0)|答案(4)|浏览(50)

In microsoft SQL, I have the following statement (roughly):

UPDATE sometable 
   SET somecolumn = @somevalue 
 WHERE somecolumn = NULL;

@somevalue is set previously in the script.

This runs fine and SQL tells me zero rows are affected. There are five rows were somecolumn is NULL. What am I doing wrong?

sql-server

来源：https://stackoverflow.com/questions/6526726/how-do-you-get-sql-to-recognize-where-column-null

4条答案

按热度按时间

y0u0uwnf1#

You have to use IS NULL instead to test for a NULL value in the column.

UPDATE sometable SET somecolumn = @somevalue WHERE somecolumn IS NULL;

赞(0）回复(0）举报 5个月前

ercv8c1e2#

Try IS NULL

UPDATE sometable 
   SET somecolumn = @somevalue 
 WHERE somecolumn IS NULL;

Source: http://blogs.msdn.com/b/sqlclr/archive/2005/06/21/431329.aspx

赞(0）回复(0）举报 5个月前

yx2lnoni3#

Joe is right. Nothing can equal null. It can only be null, so therefore,

UPDATE mytable 
SET mycolumn = @value 
WHERE anotherColumn IS NULL

赞(0）回复(0）举报 5个月前

ovfsdjhp4#

ANSI SQL defines NULL as not equaling anything - even another instance of NULL. The canonical way around that is to use IS NULL and IS NOT NULL .

There's also the MS SQL Server option SET ANSI_NULLS . Turning this option off has the effect of having WHERE x = NULL do exactly what you expect. It also, however, will include any NULL values in a query like WHERE x <> 'abc' - which may not be what you'd expect. This option is connection specific, so changing it for your connection will not affect others. You can also set a default setting at the database level. When creating a stored procedure, the option is captured at creation time - not runtime.

Another trick is to construct a query like WHERE ISNULL(x, '') = ISNULL(@x, '') . I don't think that's SARGable, so performance isn't nearly as good as WHERE (x IS NULL AND @x IS NULL) OR (x = @x) , but it's a lot nicer to write and dynamically build.

Oh - and, since we're talking ANSI SQL. The ANSI SQL version of ISNULL is COALESCE .

赞(0）回复(0）举报 5个月前

我来回答

SQL Server How do you get SQL to recognize WHERE column = NULL?

4条答案

相关问题

热门标签

最新问答