Update columns with Null values

sql oracle null sql-update
nath picture nath · Oct 13, 2010 · Viewed 84.8k times · Source

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?

Answer

codaddict picture codaddict · Oct 13, 2010

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

Related questions

Why does Oracle 9i treat an empty string as NULL?

I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is …

Read More
Oracle NVL with empty string

I have this table where NULL is the NULL value, not the string NULL: MYCOL -------- NULL example Why does this query not return the NULL row? select * from example_so where nvl(mycol, '') = '';

Read More
SQL: AVG with NULL Values

as far as i understood the AVG() function ignores NULL Values. So AVG(4,4,4,4,4,NULL) --> 4 In my case i don´t want this to happen. I need a solution like that: AVG(4,4,4,4,4,NULL) --> 3,33 without replacing the NULL …

Read More