I tried updating a table as follows:
update userloginstats set logouttime = sysdate where logouttime = null;
It didn't update the columns with null values. What went wrong?
-
1<resisting temptation to add yet another answer that says exactly the same thing...>Jeffrey Kemp– Jeffrey Kemp2010-10-14 03:36:19 +00:00Commented Oct 14, 2010 at 3:36
Add a comment
|
5 Answers
Change it to
...where logouttime is null;
^^^^^^^
NULL is a special value and we cannot use the usual = operator with it.
From the Oracle documentation for NULL:
To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN because null represents a lack of data, a null cannot be equal or unequal to any value or to another null
Comments
You cannot compare NULLs with =.
Use this:
update userloginstats set logouttime= sysdate where logouttime is null;
Comments
logouttime is null, not = null. null is never equal to anything, not even itself. Thus, the operator is.
Comments
You need to use is null not = null
update userloginstats set logouttime= sysdate where logouttime is null;
Comments
For nulls you must use "IS NULL" or "IS NOT NULL" rather than the = operator. This is because null is technically neither true or false, rather it's the absence of a value either way.
Most programming languages typically associate null with false for convenience (and thereby enabling the use of the = operator), but SQL takes a more purist approach, rightly or wrongly.
