Search code examples
sqlnullms-access-2007

SQL query not working for changing null values

I have a database table with over 18,000 rows in Access 2007.

I am trying to create a primary key but I have too many null values. Therefore, I would like to replace those null values with actual values. To do so, I wrote the following SQL statement:

UPDATE [File]
SET [Date] = '01/01/1000'
WHERE [Date] = NULL;

Unfortunately, this UPDATE statement is not working (none of the null values are changing). Can anyone tell me why?

Solution

  • Because nothing = NULL. You need to change this to use IS NULL. You also should use the ANSI dateformat.

    UPDATE [File]
SET [Date] = '1000-01-01'
WHERE [Date] IS NULL;

    This date is not valid in many DBMS. In sql server this would have to be a datetime2. I don't know about mysql or Access.